SQL Advisor

Query SQL Server with PowerShell

Get the right information on your SQL Servers via the Get-Service cmdlet.

• By Joshua Jones
• 03/10/2011

Last time, I discussed how to get PowerShell and SQLPS up and running on your machines. And much like my dog chasing its tail, you're probably asking yourself, "Now that I have it, what do I do with it?"

The first thing you'll want to learn is how to actually connect to and get information about your servers. Go to your Start | Run or Start | Search box and type SQLPS and hit Enter. If everything is installed correctly, you should see Fig. 1.

Figure 1. Get started by running SQL's version of PowerShell.

Let's start off with looking at the SQL Server services running on a given machine. Use the Get-Service cmdlet to find out about services on either your local or remote machine:

get-service –computername CSSRVSQLTST –displayname *SQL*

What you get is what you see in Fig. 2.

Figure 2. Run Get-Service on a machine and you'll get something like this.

Get-Service is not a SQLPS-specific command, but rather a PowerShell command. What happened here is we passed in a few parameters: –computername tells the command what computer to connect to (the default is localhost), and –displayname filters on the name of the service (with the help of the asterisk as a wildcard). This corresponds to the displayed name in the Services control panel. If you want to look for the actual service name, use –name.

If you want to get fancy and see how cmdlets can interact, you can sort the output of the query (resulting in Fig. 3):

get-service –computername CSSRVSQLTST –displayname *SQL* | sort-object status

Figure 3. You can sort the output of your query.

If you want to find out what else you can do with get-service, just type:

get-help get-service –examples

Now, what about actually executing some T-SQL? Most DBAs are already familiar with SQLCMD, introduced with SQL Server 2005 to replace the old OSQL command-line query interface. SQLPS uses a cmdlet called invoke-sqlcmd to call SQLCMD and pass a query to it. It supports both T-SQL and XQuery and a variety of parameters. Here's a simple query:

invoke-sqlcmd -ServerInstance CSSRVSQLTST -Database msdb –Query "Select top 2 * from sys.objects"

Figure 4. Call up SQLCMD and you get this. (Click image to view larger version.)

Here's what you should take away from the resulting Fig. 4:

1. It's an easy command, and
2. That is not a great way to get data back if you're going to do anything with it.

If you're going to be executing a stored procedure or querying for one or two very specific rows from a table/view, this method is fine and very easy. But if you're looking at a bunch of rows coming back, there’s a better way.

PowerShell provides some cmdlets that you can pipe the result set to in order to get a better looking format. Here's how to export the results to a CSV file:

invoke-sqlcmd -ServerInstance CSSRVSQLTST -Database msdb –Query "Select top 2 * from sys.objects" | Export-Csv –path C:\MyFiles\results.csv

When run, this command returns no output to the command-line screen. Instead, it dumps the file to the specified directory. Using this method will get you a slightly more useful list of rows to review. Again, use get-help invoke-sqlcmd to get more info.

Figure 5. Analyzing information is better if you use PowerShell to dump the results to a CSV file. (Click image to view larger version.)

At this point, you have enough information to be dangerous! Be sure you're careful when querying any production servers, because if you have the right credentials, you could do a lot of damage.

Next time, I'll talk about how to create scripts using basic PowerShell and SQLPS cmdlets that access system objects in SMO and Windows. Until then, have fun!