Where[-Object] clauses for people who use SQL Server
Summary: Learn about the power of the Where[-Object] cmdlet in PowerShell.
Can I do a WHERE clause with an IN, in PowerShell?
Yes!
Today’s post is from Aaron Nelson ( blog | twitter ), who is a Microsoft MVP for SQL Server (Data Platform), leads the PowerShell Virtual Chapters of PASS, volunteers for the local PASS Chapter AtlantaMDF, and helps organize SQL Saturday events in Atlanta.
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.
Where[-Object] is an extremely important cmdlet. Those of us who use SQL are accustomed to a rather rich experience when it comes to our WHERE clauses. Can we expect the same richness from Where[-Object]? While Where[-Object] does have -and and -or, we’re used to more than just that. Today’s 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.
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, if T-SQL is an option, you should usually use that unless the task wouldn’t be easier to do in PowerShell. What is something that’s easier to do in PowerShell than it is T-SQL, you ask? Backup databases, but that’s a story for another day.
I wanted to show an example of how to do the PowerShell equivalent of this query:
SELECT name ,
SCHEMA_NAME([schema_id]) AS 'SCHEMA_NAME'
FROM SYS.tables
WHERE SCHEMA_NAME([schema_id]) = 'Production'
AND Name IN ('Product', 'TransactionHistory', 'TransactionHistoryArchive', 'WorkOrder', 'WorkOrderRouting')
I knew that I could stack WHERE clauses and use -or, which leads me to the following statement. This code does work, but it’s 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 syntax.
A benefit of PowerShell over SQL syntax is that I can stack WHERE 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 in succession like I can in PowerShell. It is a handy, but arguably lazy, coding trick that I am pointing out.)
DIR SQLSERVER:\SQL\LocalHost\DEFAULT\Databases\AdventureWorks2014\tables |
WHERE {$_.schema -eq 'Production'} |
WHERE {$_.Name -eq "Product" -or
$_.Name -eq "TransactionHistory" -or
$_.Name -eq "TransactionHistoryArchive" -or
$_.Name -eq "WorkOrder" -or
$_.Name -eq "WorkOrderRouting"}
I can shrink it a little by combining the $_.Schema portion, but I’m looking for a lot more (or in this case, less).
DIR SQLSERVER:\SQL\LocalHost\DEFAULT\Databases\AdventureWorks2014\tables |
WHERE {$_.schema -eq 'Production' -and $_.Name -eq 'Product' -or
$_.Name -eq 'TransactionHistory' -or
$_.Name -eq 'TransactionHistoryArchive' -or
$_.Name -eq 'WorkOrder' -or
$_.Name -eq 'WorkOrderRouting'}
I wanted a more efficient way, so I went searching. Eventually, I found this MSDN page, which did have a valid example, but it was literally the only reference for the -in operator on Where[-Object] that I have been able to find.
Get-Process | where -Property ProcessName -in -Value "Svchost", "TaskHost", "WsmProvHost"
Using that example, I was able to come up with this:
<# -In FTW! #>
DIR SQLSERVER:\SQL\LocalHost\DEFAULT\Databases\AdventureWorks2014\tables |
WHERE {$_.schema -eq 'Production'} |
WHERE -Property Name -In -Value "Product",
"TransactionHistory",
"TransactionHistoryArchive",
"WorkOrder",
"WorkOrderRouting";
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 clauses again. This is because the example use the feature, which was introduced in in PowerShell 3.0, that allowed you to drop the {} for a single operator.
The only question is: Is it efficient? What if that first WHERE 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 WHEREs without having to use the pipeline?
Well, you can use the Where() method, but it’s less readable. I would only attempt the following syntax if I was sure that I needed to focus on pipeline efficiency. The syntax isn’t really all that tough to read. In fact, it’s kind of easier to read if you think of it as putting the DIR portion into a subquery. But, still, it might be overly confusing for a coworker who’s trying to debug my code while I’m out on vacation or something.
Note: The .Where() method is available in PowerShell 4.0 and later versions. Also note that I dropped the -Value operator because it doesn’t seem to be strictly necessary.
(DIR SQLSERVER:\SQL\LocalHost\DEFAULT\Databases\AdventureWorks2014\tables ).WHERE({$_.schema -eq 'Production'}) |
WHERE -Property Name -In "Product",
"TransactionHistory",
"TransactionHistoryArchive",
"WorkOrder",
"WorkOrderRouting";
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’t find a single example that called it out for being this easy. Hence, the reason for this post.
<# Best way I have found so far. #>
DIR SQLSERVER:\SQL\LocalHost\DEFAULT\Databases\AdventureWorks2014\tables |
WHERE {$_.schema -eq 'Production' -and $_.Name -In 'Product',
'TransactionHistory',
'TransactionHistoryArchive',
'WorkOrder',
'WorkOrderRouting'}
This is stunningly similar to SQL syntax, at least to someone who has also been writing PowerShell code for the last seven years. It’s so close, I will point out the differences in case you’re a SQL person and need a guide to do this in PowerShell.
- You need to put
{}around the entire portion of theWHEREclause that actually comes after the word,WHERE. - We need to add
$_.in front of our column names. - Use
-eqinstead of=. - Add a
–in front ofANDandIN(and in front ofORif you were using that).- Side note: I’m not demonstrating it today, but PowerShell does support wrapping portions of your
WHEREclause that you want to logic together by using()which, as we all know, can be extremely important when you start stickingORs in yourWHEREclause.
- Side note: I’m not demonstrating it today, but PowerShell does support wrapping portions of your
Now, when I say, “Best way I have found so far”, I’m talking about from a code readability standpoint. Again, if my DIR is going to produce 10,000+ tables, I should probably go with the .Where() method. However, if I’m only dealing with a few hundred, piping to WHERE should turn out fine.
Also, you might be asking: Can I provide an array as the values for the -In? Yes, yes you can!
[array]$tblz = 'Product','TransactionHistory','TransactionHistoryArchive','WorkOrder','WorkOrderRouting';
DIR SQLSERVER:\SQL\LocalHost\DEFAULT\Databases\AdventureWorks2014\tables |
WHERE {$_.schema -eq 'Production'} |
WHERE -Property Name -In $tblz;
At this point, you’re probably thinking “Shouldn’t I just do this in T-SQL?” If you’re querying tables within a single database, yes, probably. If, on the other hand, you are trying to assemble information about tables in multiple databases and/or across multiple SQL instance, that’s a different story.
On many occasions, I find myself using both PowerShell and T-SQL for what they’re good at, at the same time. The following example does just that.
In this script, we’re going to poke around our environment for some potential space savings. In the PowerShell portion, we’re going to iterate through our Registered Servers from SQL Server Management Studio and look at all the databases on those servers in which IndexSpaceUsage is more than 30% of the size of the DataSpaceUsage for the database. When PowerShell finds a database like that, it is going to fire off a T-SQL script to find indexes in that database that have been updated but not selected from since the last server reboot. When the T-SQL query finds one of those indexes, it passes that information back to PowerShell in the form of a .NET DataTable.
What to do with those indexes from there is up to you, but now you know about them.
<#
.DESCRIPTION
Goes through all the SQL Server instances in your Registered Servers and looks for databases
where more than 30% of the Data Space Used is taken up by indexes, then looks inside those
databases for Indexes that have been updated but not scanned, seeked, or lookuped since
the last server reboot.
#>
[array]$DemoDBs = 'AdventureWorks2012', 'AdventureWorks2014'
$QueryIWantToRun = "SELECT @@SERVERNAME AS 'ServerName',
CASE @@SERVICENAME WHEN 'MSSQLSERVER' THEN 'Default' ELSE @@SERVICENAME END AS 'InstanceName',
database_id ,
db_name(database_id) AS 'DatabaseName',
Schema_Name(t.[schema_id]) AS 'SchemaName',
t.name AS 'TableName',
i.object_id ,
i.name AS 'IndexName',
i.index_id,
i.type_desc ,
OBJECT_NAME(iu.object_id) AS 'ObjectName',
user_seeks ,
user_scans ,
user_lookups ,
user_updates ,
last_user_seek ,
last_user_scan ,
last_user_lookup ,
last_user_update
FROM SYS.TABLES T
JOIN SYS.indexes i
ON i.object_id = t.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats iu
ON iu.object_id = i.object_id
AND iu.index_id = i.index_id
WHERE iu.database_id = DB_ID()
AND i.type_desc = 'NONCLUSTERED'
AND last_user_seek IS NULL
AND last_user_scan IS NULL
AND last_user_lookup IS NULL
AND last_user_update IS NOT NULL"
foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | WHERE {$_.Mode -ne 'd'} )
{
foreach($DB IN Get-SqlDatabase -ServerInstance $RegisteredSQLs.Name |
WHERE {$_.IsSystemObject -EQ $false -and (($_.IndexSpaceUsage / $_.DataSpaceUsage) * 100) -GT 30 -and $_.Name -notin $DemoDBs}
)
{
$Results+= Invoke-Sqlcmd -ServerInstance $RegisteredSQLs.Name -Database $DB.Name -Query $QueryIWantToRun -OutputAs DataTables
}
}
$Results | Format-Table -AutoSize;
At the end of the day, PowerShell WHERE syntax behaves a lot like SQL syntax, at least for the basic operators that I tested (Where, -and, -in, and -or). It can even wrap portions of your WHERE clause in parentheses (), and that also behaves like SQL syntax. It is a wonderful capability to be able to use!
Aaron Nelson Microsoft MVP for SQL Server (Data Platform)
Light
Dark
0 comments