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:

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

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:

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:

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:
   SQL

   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:

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.