November 9, 2021 by Kenneth Fisher
Sometimes when you are trying to figure something out you run across something really interesting. In this case I discovered that Powershell has a WHERE clause. Sort of. It’s actually a separate command called Where-Object. I noticed it while I was trying to figure out why I couldn’t get a command to work with a pipeline correctly. Unfortunately I lost the link where I found it but here is the command:
Get-Help Invoke-Sqlcmd -Parameter * | Where-Object pipelineinput -Like 'true*'
A character string or SMO server object specifying the name of an instance of the Database Engine. For default instances, only specify the computer name: “MyComputer”. For named instances, use the format “ComputerName\InstanceName”.
Accept pipeline input? true (ByValue)
Accept wildcard characters? false
If you run just the Get-Help portion of the command you’ll get a list of all of the parameters of Invoke-Sqlcmd. This can be handy but I just want to look at the parameters that meet a certain criteria. In this case that the pipelineinput is true. Unfortunately we appear to be working with text here, not a flag of some type, so you have to use the -Like operator rather than -EQ (equals). Regardless, from what I can tell Where-Object acts similar to a WHERE clause in a query for the pipeline. You can put it anywhere you want in the pipeline (from what I can tell) and you can probably even put it in there multiple times, but still, I think any DBA will be perfectly comfortable using it.