21 characters to paradise

Powershell gives SQL Server a huge boost

I've been playing with Powershell off and on for about a year, but I was never really very serious about it. But this year at TechED I spent some time looking at the new powershell features in Katmai and what can I say... I'm officially in love. No, wait a minute... this is melting hot lust.

Let me just say that the way the powershell provider in Katmai has been implemented as a minishell has come under some criticism. And maybe they have a point. There are some thing you can't do, and I'm not going to go into those as they're all very well documented in different blogs. What I am going to go into is what you CAN do with powershell in SQL Server. That's right, I said SQL Server, and not just Katmai. Because you can connect to any Yukon or SQL2K box and run powershell against them. I think there are some prereqs there like SQL2K SP4 and maybe SQL2K5 SP1, but you can look that stuff up yourself. The point here is that you're not limited to Katmai.

OK, so rather than spend lots of time talking you through little things, I'm just going to give you a couple examples here.

To list all tables and their rowcounts:

Open powershell in SSMS and type the following: I'm using Northwind.

cd default\databases\Northwind\tables

So just like in DOS you navigate DB structures like drives.

Now type:

gci | sort-object -Property RowCount -desc | format-table Schema, Name, Rowcount -autosize

In short, I'm getting all the tables, sorting them by rowcount in descending order and then piping the output to a table with the Schema, Name, and RowCount columns. And autosize just tightens up the table size. Try this command with and without the -autosize parameter and you'll see what I mean.

So that's pretty cool right? You can get all the tables and their rowcounts w/o a cursor or dynamic SQL or a #table. Now, let's filter a little bit. The same command comes back...

gci *customer* | sort-object -Property RowCount -desc | format-table Schema, Name, Rowcount -autosize

Now you see the same command with *customer* in it. This tells it that you only want tables that have 'customer' in them. So in SQL this is equivalent to "like '%customer%'".

So far so good?

OK, one more because this one is just tremendous.

Start in the same location as before:

For completion, you'll be here:

cd default\databases\Northwind\tables

Now type this:

gci | % {$_.Script()}

That's only 21 characters. But what does it do?

It scripts every table in your DB is what it does. No cursors, no gui, no manually watching it. Just a script of your entire table scructure. What's that worth to you? And you can schedule it and add it to jobs, etc. Now, that will just spit out the script to the screen so you need a way to do something useful with it. So for now, let's put the script into a txt file.

So the same line again with the txt file stuff in it:

gci | % {$_.Script()} | out-file C:\Table.txt

That's it. Now do you think you can also filter the tables to be scripted and only get certain tables? I bet you can do that pretty easily like this:

gci *customer* | % {$_.Script()} | out-file C:\Table.txt

Now, there's a richer filter object you can pipe to, and I'll get into that some other time. I just wanted you to be able to get started.

So seriously, what do you think this is worth? You don't have to script things by hand anymore and when you do it's a much shorter trip than it used to be. Writing this type of script in SMO/DMO or SQL is very long and problematic. That's why not many people have written them. But now you can script all your tables in just 21 characters (including spaces). And how much do you wanna bet you can script out your SPs, views, etc the same way. I'd be pretty willing to bet you can, huh? All you have to do is navigate to the right "drive". So in each one you can run the 'dir' command to see your available options.

Good luck.