21 characters to paradise

Powershell gives SQL Server a huge boost

Follow @infoworld

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()}