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.

cls
$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.