At the SQL Connections conference in April of 2008 I gave a presentation on some of the new Manageability Improvements for SQL Server 2008. In this blog post I’ll talk about one of those new improvements – our new PowerShell provider for SQL Server.
For a couple of days now I’ve posted about the basics of PowerShell. I mentioned that it has a few advantages that I think the DBA will find essential:
1. It’s like a batch-file based system (all of your old logic will still work)
2. It lets variables stand for objects (see the post yesterday for this)
3. It has piping (the | symbol)
I’ve only covered the basics of PowerShell, but there are lots of good links out on the web for it. What is interesting for the DBA is the fact that we have included a “Provider” for PowerShell that has the ability to work with the Server Management Objects (SMO). That means that all of the command-lets and functionality you have in PowerShell you can use with SQL Server.
You’re able to launch PowerShell in two ways: You can right-click most objects in SQL Server Management Studio (SSMS); and you can drop to a command-prompt and type sqlps.exe. If you have PowerShell installed already, you can load the libraries for SMO manually. I posted a link to that process a few days ago.
So once you’re in PowerShell for SQL Server, what can you do? Well, let’s assume you’re at the prompt of a database:
You can type this:
This will show all of the tables. While that’s useful, let’s take that piping idea I explained yesterday and get a little more useful information:
DIR | Sort-Object -Property RowCount -desc | Format-Table schema, name, rowcount -AutoSize
Let’s take a look at this – first, the DIR command lists the tables. This list is sent to the Sort-Object command-let that sorts the list, using a switch to use the row count for the sort, descending. Finally the result of that list is sent to the Format-Table command-let that takes the result and makes a neat table from it, using only the schema, name and rowcount values. A switch makes that fit on the screen.
You could add another pipe to the file output like I did yesterday. Then you could schedule this for each day, and create a report of your largest tables.
I’ll use the PowerShell subsystem a lot more in future blogs. It’s a very powerful new feature!