This post expands on the basics of SQL Server PowerShell and introduces “piping” as well as using Select in PowerShell cmdlets. The first post can be found here.
Technet defines “piping” as “passing that object – unchanged – from one part of the command to another”. The definition speaks for itself and accurately describes what occurs when “piping” in a cmdlet. To demonstrate this, open a SQL PowerShell window from within the databases.
Running the dir command returns a list of the databases and some of their properties:
Now pipe the directory results to a select cmldet and you will see that there are a number of properties that aren’t returned with just a directory command.
dir | Select *
Now let's refine the command to be a bit more selective and only return the database name and recovery model.
dir | Select name, RecoveryModel
Now we can pipe the results of the select to a sort to operator to sort the results based on the recovery model.
dir | Select name, RecoveryModel | Sort RecoveryModel
So now let's filter the results to show only the records of the databases set to Simple recovery model and then pipe those results to a sort operator based on the name in descending order.
dir | Select name, RecoveryModel | Where-Object {$_.RecoveryModel –eq "Simple"} | Sort RecoveryModel
It is true that there are other methods to pass objects or results such as using variables, but “piping” provides an easy means of accomplishing this and requires less code.
