Using Powershell with SQL Server

Anything that can be done with ADO.NET is possible in PowerShell through a call to Assembly::Load and the New-Object cmdLet, but that doesn’t mean that the resulting code will seem very shell-like or practical. Two approaches to database access that definitely do seem compelling to me, however, are the sample SQL Provider recently demoed by the PowerShell team and Dan Sullivan’s PowerSMO script, which is really just a thin wrapper around the SQL Management Objects found in the Microsoft.SqlServer.Management.Smo namespace. The SQL Provider allows traditional command line navigation through the database using dir and cd and the SMO objects provide access to nearly any administrative function in SQL Server.

In order to use the PowerShell team’s SQL Provider, you’ll need to download SQLProvider.dll, install it in the GAC, and call the Add-PSSnapin cmdLet. Then you can treat the database just like the filesystem and cd or dir your way around with statements such as the following:


PS>cd SQL:

SQL:>cd MySQLServerName

SQL:>dir

master
tempdb
model
msdb
pubs
Northwind

SQL:>cd Northwind

SQL:>dir

name
—-
Categories
CustomerCustomerDemo
CustomerDemographics
.. etc…

SQL:>cd Employees

SQL:>Dir -filter {where EmployeeId=8}

EmployeeID      : 8
LastName        : Callahan
…etc…

Unfortunately, this provider is only a partially implemented sample and I quickly lost interest when I figured out that I couldn’t easily or naturally pipe the results to the where, select, and sort cmdLets. I did some quick searching to see if someone else had taken the next step and fully implemented a sql provider but I didn’t come across anything. However, I’m sure it won’t be long before somebody creates a production-ready version. The PS team did include the code for their sample provider, so perhaps I’ll take a look at it myself to see how hard it is.

To run the PowerSMO examples found on Dan Sullivan’s blog, you simply need to run a powershell script or place it in your profile so that it is run automatically when you start the shell. The script preloads some required dll’s and provides a factory function for retrieving some of the key objects, so retrieving a server object feels more like a cmdLet experience.

$server = get-SMO_Server mySQLServerName

However, since he warns that his script is for instructional purposes only, you might also just want to load the server object directly: 

[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo,Culture=Neutral,Version=9.0.242.0,PublicKeyToken=89845dcd8080cc91″) | out-null

$server = new-object “Microsoft.SqlServer.Management.Smo.Server” myServer

Then you can grab a database object and do interesting things like finding which tables and indexes in your database are taking up the most space.

$pubs = $s.databases | ?{$_.name -eq “pubs”}

$pubs.tables | ?{$_.name -notlike “sys*”} | select name, dataspaceused,indexspaceused,@{name=”Total Space”;expression={$_.dataspaceused + $_.indexspaceused}} | sort dataspaceused -desc

…or finding out the firve widest tables in the database as potential candidates for normalization and save it to an html file.

 $pubs.tables | ?{$_.name -notlike “sys*”} |select name, @{name=”Columns”;expression={$_.columns.count}} -first 5 | sort columns -desc | convertto-html > C:\wideTables.html

Again, you can do almost any of these functions through some other way (system sproc, sql function, utility), but what is significant is the capabilities that powerShell gives you to be able to manipulate the resulting data once you have it (sort, group, filter, format, etc.).

1 Comment

  1. lb July 28, 2007 11:22 am 

    woah! a very nice start!

    thank’s for telling us about it!

Leave a Reply