The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.
Basically, you can now open up PowerShell, and change directory to the PowerShell drive “SQL:”. Then change directories through the instances, databases, tables, and so on. At any point, try something like “dir | gm” (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.
Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO – when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions – I found this a little disappointing, and I haven’t figured out the best way of getting help on methods that are on these objects either.
It’s definitely a good start though – should be a very nice feature of SQL Server 2008.