Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

A SQL Cmdlet a Day 1

, 3 Apr 2014
Rate this:
Please Sign up or sign in to vote.
A SQL Cmdlet a day 1

Have you ever had the desire to work with SQL using a command line interface? If you answered “Why would I ever want to do that?” I can give you several EXCELLENT reasons:

  1. It’s Kewl!!
  2. It will impress your friends!!
  3. Chicks LOVE IT!!

ALL RIGHT!! So maybe I’m exaggerating. Regardless of my listed reasons, PowerShell is one more tool available to leverage management and administration of SQL. This post kicks off a series on working with SQL using PowerShell.

PowerShell is Microsoft’s primary command line interface for the majority of its software products and, as it is built on .NET, there is a lower learning curve for existing .NET developers. SQL and PowerShell integration began with SQL 2008 and it provides another means of managing, administrating, and automating SQL Server.

Starting with SQL Server 2008, you may have noticed the additional “Start PowerShell“menu option when right clicking within the object explorer:

image

Selecting this option opens up a PowerShell command line interface within the context of where you clicked in the Object Explorer.

image

The first thing that might jump out to you is that the command window background is black and not the Windows PowerShell blue as well as the prompt shows the directory context of where the object explorer was right clicked, in this case Databases. Obviously this is a PowerShell prompt, but it differs from the Windows PowerShell prompt in that it has already loaded the assemblies required to work with SQL Server and places you in the context of where you initiated the prompt. This allows you to pass simple and familiar commands like “dir”, directory, which will return all the objects in that context. The below screenshot shows that executing the “dir” command displays all the databases and associated properties from within the current instance:

image

Pretty kewl right!! Well what if you want to use PowerShell to view all of the jobs within SQL Server agent? Easy enough! Again this can be done using command shell 101. First, we need to change the directory to the SQL Server agent jobs location. For this specific instance, I will use my “cd”,change directory, command and specify the path for SQL Server agent jobs:

cd \SQL\INSTANCENAMEHERE\Default\JobServer\Jobs

From the context of the jobs, I now only have to run another “dir”, directory command:

image

This shows how you can use PowerShell to display objects from within SQL, but what is the real value of this? I mean it is pretty cool to be able to do this from a command line interface, but how exactly can this simple command be used to simplify administration?? Consider that your management team has request a list of all jobs running on an instance of SQL. This could be done in several different ways:

  1. Open jobs in SQL agent and get a screen shot
  2. Execute a T-SQL statement to return this from the msdb database and paste the results into a file:
    USE msdb;GO
    SELECT nameFROM sysjobs;GO
  3. Use PowerShell to directly pipe the results to a text file
    dir > d:\SQL\jobs.txt

And the results look like this:

image

This demonstrates a VERY basic introduction to using PowerShell for SQL Server. My next post will examine piping cmdlets as well as using Select in PowerShell.

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.
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 4 PinmemberRajesh Varma Buddaraju6-Apr-14 2:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 3 Apr 2014
Article Copyright 2014 by derekman9707
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid