A SQL Cmdlet a Day 2 Piping and Select

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

image

Running the dir command returns a list of the databases and some of their properties:

image

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 *

image

Now lets refine the command to be a bit more selective and only return the database name and recovery model

dir | Select name, RecoveryModel

imageNow 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

image

So now lets 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

image

It is true that there are other methods to pass objects or results such as using variables, but “piping” provide an easy means of accomplishing this and requires less code.

Comments ( 2 )

Leave a Reply


Hit Counter provided by Sign Holders