Click here to Skip to main content
12,632,970 members (33,173 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

3.2K views
2 bookmarked
Posted

A SQL Cmdlet a Day 2 Piping and Select

, 5 Apr 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
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 let's 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 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

image

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

derekman9707
Database Developer
United States United States
David retired as a Sergeant with the Cape Coral Police Department after 22 years of service. His final 10 years of duty were as a database administrator and developer in the Administrative Services Division. He began his career with the police department in 1990 in the patrol division and worked various assignments until being promoted to Sergeant in 1998. Based on his education and experience David was assigned to Administrative Services in 2002 and was responsible for database administration, software integration, and development for public safety. David’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.

You may also be interested in...

Pro

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161208.2 | Last Updated 5 Apr 2014
Article Copyright 2014 by derekman9707
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid