{"id":81255,"date":"2017-01-04T00:01:32","date_gmt":"2017-01-04T08:01:32","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/?p=81255"},"modified":"2019-02-18T09:10:15","modified_gmt":"2019-02-18T16:10:15","slug":"where-object-clauses-for-people-who-use-sql","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/where-object-clauses-for-people-who-use-sql\/","title":{"rendered":"Where[-Object] clauses for people who use SQL Server"},"content":{"rendered":"

Summary:<\/strong>\u00a0Learn about the power of the\u00a0Where[-Object] cmdlet in PowerShell.<\/span>\n\"Hey, Can I do a WHERE clause with an IN, in PowerShell?<\/p>\n

\"Hey,\u00a0Yes!<\/p>\n

Today\u2019s post is from Aaron Nelson ( blog<\/a> | twitter<\/a> ), who is a Microsoft MVP for SQL Server (Data Platform), leads the PowerShell<\/a> Virtual Chapters of PASS, volunteers for the local PASS Chapter AtlantaMDF<\/a>, and helps organize SQL Saturday<\/a> events in Atlanta.<\/p>\n

The PowerShell Virtual Chapter of PASS hosts monthly sessions on SQL Server + PowerShell, and you can find the recordings of those sessions on their YouTube channel<\/a>.<\/p>\n

Where[-Object]<\/code> is an extremely important cmdlet. Those of us who use SQL are accustomed to a rather rich experience when it comes to our WHERE<\/code> clauses. Can we expect the same richness from Where[-Object]<\/code>? While Where[-Object] does have -and<\/code> and -or<\/code>, we\u2019re used to more than just that. Today\u2019s post is an effort to get the word out about some options that were added starting back in PowerShell 3.0 because I found shockingly few examples of what I was trying to do.<\/p>\n

Before we begin, for comparison purposes, I will show how to do the same thing and receive the same results in both PowerShell and T-SQL. To be clear<\/u>, if T-SQL is an option, you should usually use that unless the task wouldn\u2019t be easier to do in PowerShell.\u00a0 What is something that\u2019s easier to do in PowerShell than it is T-SQL, you ask? Backup databases, but that\u2019s a story for another day.<\/p>\n

I wanted to show an example of how to do the PowerShell equivalent of this query:<\/p>\n

SELECT name ,<\/code><\/p>\n

SCHEMA_NAME([schema_id]) AS 'SCHEMA_NAME'<\/code><\/p>\n

FROM SYS.tables<\/code><\/p>\n

WHERE SCHEMA_NAME([schema_id]) = 'Production'<\/code><\/p>\n

AND Name IN ('Product', 'TransactionHistory', 'TransactionHistoryArchive', 'WorkOrder', 'WorkOrderRouting')<\/code><\/p>\n

I knew that I could stack WHERE<\/code> clauses and use -or<\/code>, which leads me to the following statement. This code does work, but it\u2019s a chunky syntax, and I actually had a lot more than just five tables that I needed to query. The code would get verbose very quickly, so I wanted smaller<\/em> syntax.<\/p>\n

A benefit of PowerShell over SQL syntax is that I can stack WHERE<\/code> clauses, and if I need to, I could comment out one without having to rewrite the other. (Specifically, in the SQL language, I am not able to use the WHERE<\/code> in succession like I can in PowerShell. It is a handy, but arguably lazy, coding trick that I am pointing out.)<\/p>\n

DIR SQLSERVER:\\SQL\\LocalHost\\DEFAULT\\Databases\\AdventureWorks2014\\tables |\nWHERE {$_.schema -eq 'Production'} |\nWHERE {$_.Name -eq \"Product\" -or\n$_.Name -eq \"TransactionHistory\" -or\n$_.Name -eq \"TransactionHistoryArchive\" -or\n$_.Name -eq \"WorkOrder\" -or\n$_.Name -eq \"WorkOrderRouting\"}<\/code><\/p>\n

I can shrink it a little by combining the $_.Schema<\/code> portion, but I\u2019m looking for a lot more (or in this case, less).<\/p>\n

DIR SQLSERVER:\\SQL\\LocalHost\\DEFAULT\\Databases\\AdventureWorks2014\\tables |\nWHERE {$_.schema -eq 'Production' -and $_.Name -eq 'Product' -or\n$_.Name -eq 'TransactionHistory' -or\n$_.Name -eq 'TransactionHistoryArchive' -or\n$_.Name -eq 'WorkOrder' -or\n$_.Name -eq 'WorkOrderRouting'}<\/code><\/p>\n

I wanted a more efficient way, so I went searching. Eventually, I found this MSDN page<\/a>, which did have a valid example, but it was literally the only<\/u> reference for the -in operator on Where[-Object]<\/code> that I have been able to find.<\/p>\n

Get-Process | where -Property ProcessName -in -Value \"Svchost\", \"TaskHost\", \"WsmProvHost\"<\/code><\/p>\n

Using that example, I was able to come up with this:<\/p>\n

<# -In FTW! #>\nDIR SQLSERVER:\\SQL\\LocalHost\\DEFAULT\\Databases\\AdventureWorks2014\\tables |\nWHERE {$_.schema -eq 'Production'} |\nWHERE -Property Name -In -Value \"Product\",\n\"TransactionHistory\",\n\"TransactionHistoryArchive\",\n\"WorkOrder\",\n\"WorkOrderRouting\";<\/code><\/p>\n

This is great and exactly what I need. Note, to match the syntax of the example and get it to work, I was forced to use two WHERE<\/code> clauses again. This is because the example use the feature, which was introduced in in PowerShell 3.0, that allowed you to drop the {}<\/code> for a single operator.<\/p>\n

The only question is: Is it efficient? What if that first WHERE<\/code> on the schema name is only trimming it down to 10,000 tables? It could happen. So, the question is: Is there a way to do both WHERE<\/code>s without having to use the pipeline?<\/p>\n

Well, you can use the Where()<\/code> method, but it\u2019s less readable. I would only attempt the following syntax if I was sure that I needed to focus on pipeline efficiency. The syntax isn\u2019t really all that<\/em> tough to read. In fact, it\u2019s kind of easier to read if you think of it as putting the DIR<\/code> portion into a subquery. But, still, it might be overly confusing for a coworker who\u2019s trying to debug my code while I\u2019m out on vacation or something.<\/p>\n

Note<\/strong>: The .Where()<\/code> method is available in PowerShell 4.0\u00a0and later versions.\u00a0Also \u00a0note\u00a0that I dropped the -Value<\/code> operator because it doesn\u2019t seem to be strictly necessary.<\/p>\n

(DIR SQLSERVER:\\SQL\\LocalHost\\DEFAULT\\Databases\\AdventureWorks2014\\tables ).WHERE({$_.schema -eq 'Production'}) |\nWHERE -Property Name -In \"Product\",\n\"TransactionHistory\",\n\"TransactionHistoryArchive\",\n\"WorkOrder\",\n\"WorkOrderRouting\";<\/code><\/p>\n

After digging some more, I learned that the answer I was looking for all along was surprisingly close to SQL syntax, but oddly, I couldn\u2019t find a single example that called it out for being this easy. Hence, the reason for this post.<\/p>\n

<# Best way I have found so far. #>\nDIR SQLSERVER:\\SQL\\LocalHost\\DEFAULT\\Databases\\AdventureWorks2014\\tables |\nWHERE {$_.schema -eq 'Production' -and $_.Name -In 'Product',\n'TransactionHistory',\n'TransactionHistoryArchive',\n'WorkOrder',\n'WorkOrderRouting'}<\/code><\/p>\n

This is stunningly similar to SQL syntax, at least to someone who has also been writing PowerShell code for the last seven years. It\u2019s so close, I will point out the differences in case you\u2019re a SQL person and need a guide to do this in PowerShell.<\/p>\n