Replicating the SQL ‘IN’ Statement in Powershell

I’ve been taking the sqlskills class this week (mind-numbingly good class btw, will post more later when my brain cools down) and lucky for me Aaron Nelson (blog | twitter) is taking the class as well.  So, this gave me a chance to ask something which I’ve been trying to figure out how to replicate in powershell for a few months now.  Naturally, it took him mere seconds to figure it out.

I’ve been wanting to replicate the functionality of the sql ‘in’ statement on the pipeline, but I could never get it to work correctly.  Here is how you can get this to work.  You’ll need SQLPSX installed to get this to work.

$findTables = 'Servers','Jobs','Location'
Get-SqlDatabase -sqlserver ServerName -dbname DatabaseName | Get-SqlTable |
where{$findTables -contains $_.Name} |
select schema, name

At first I was a bit perplexed at how this was working, but after a few cups of coffee it made more sense.  What I had been confused about was the order of the statements in the where{} cmdlet.  I had been of the mindset that the where{} was returning a set of objects.  Obviously, this is not the case.  All it is doing is filtering table arraylist. 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.