Getting Started with PowerShell and SMO

Microsoft will introduce a new shell with the forthcoming release of Vista. It was code-named Monad initially. Later on it was referred to as MSH (Microsoft Shell). In April, it was officially named as PowerShell. In fact, PowerShell RC2 (Release Candidate 2) is available for download now.

I’ve downloaded and played with RC2 and was pretty impressed so far. PowerShell borrowed heavily from the *nix world, such as tab completion, aliasing, variable naming, output piping. Some of the aliases are actually named just like *nix commands, such as cat, ps, ls, etc., with very similar functionalities.

PowerShell’s syntax and keywords seem to be pretty clean, with a very limited verbs used in the entire shell (get, set, invoke, out, new, and so on).

Since it will come with Vista, naturally it has tight integration with the .Net Framework (Did I tell you that I absolutely hate this .Net lingo Microsoft marketing invented?). So it can load .Net assemblies and use the rich objects and methods within them. In fact, that is where my current interest is: combining PowerShell with SMO to do some nifty stuff.

Today, let me show off something I just figured out earlier tonight: scripting database objects out. It is pretty rudimentary, but I think it is something I can build on.

Two assumptions:
1. Assume you have Sql Server 2005 installed with a database called test and table called t1;
2. Assume you have PowerShell RC2 on this machine.

1. Open PowerShell;
2. [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
3. $MyServer = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) “MyDatabaseServer”
4. $MyScripter = new-object (‘Microsoft.SqlServer.Management.Smo.Scripter’)
5. $MyScripter.Server=$MyServer
6. $MyScripter.Script($MyServer.Databases[“test”].tables)

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[t1](
[c1] [int] NULL
) ON [PRIMARY]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.