Combining SMO and Powershell to Generate SQL Database Schema

By Rajib Bahar at December 08, 2009 10:49
Filed Under: .NET, SQL

There are times we find the need to generate the database schema. In SQL Server, it can be easily done using the graphical wizards in the Management Studio. I haven't found a way to script it to this day.


However, one alternative solution to this is to combine .NET programmability feature in powershell, and SMO. With this approach you can setup a powershell script job to automate your team's database build process.


Here are some basic assumptions before reading this post:

1. SQL Server 2008 is installed

2. Powershell is installed

3. SMO is in the GAC (Global Assembly Cache) or you know how to register it there

4. AdventureWorks is loaded in the database


Here are the steps I took to generate script against AdventureWorks database:


First of all, I went to management studio and right clicked on the AdventureWorks database to "Start Powershell"


PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\1...


At this point, the SMO object was loaded using .NET reflection technology. Next we declare $srv variable and assign the local SQL Server 2008 instance to it. Then we assign the AdventureWorks database to the $db variable. The database object has an overloaded method namely Script(). We need to invoke that method to generate the database script. See the output below as the script is run.


PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $srv = new-object("Microsoft.SqlServer.Management.Smo.Server") "(local)\sql2k8"
PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db = $srv.Databases["AdventureWorks"]

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db

WARNING: column "Owner" does not fit into the display and was removed.

Name                        Status          Recovery Model   CompatLvl        Collation
----                           ------          --------------           ---------            ---------
AdventureWorks       Normal          Simple                 100            SQL_Latin1_General_CP1_CI_AS

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db.Script()

( NAME = N'AdventureWorks_Data', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH =16384KB )
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 18432KB , MAXSIZE = 2048GB , FILEGROWTH = 1638
4KB ) COLLATE SQL_Latin1_General_CP1_CI_AS
    EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'


The limitation we run with the above approach is that it doesn't include the objects such as tables, stored procedures, and functions in the script. That's why we have to write a loop to iterate all the tables, procedures, checks, primary, functions, etc. Each of those classes have the Script() method and we can invoke them as we need it. 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> for ($i=0; $i -lt $db.Tables.Count; $i++) {$db.Tables[$i].Script()}

Yes, there are more gotchas. :(


So far, we have looked into resolving this issue using 1 of the overloaded Script() method. The 2nd version of the overloaded method expects ScriptingOptions as one of the parameter. Here is how we would declare them and the options they give us.

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $sc = new-object("Microsoft.SqlServer.Management.Smo.ScriptingOptions")

Here is a quick list of properties we can set on the $sc (ScriptingOptions) object.

            $sc.AppendToFile = 0;
            $sc.Bindings = 1;
            $sc.Default = 1;
            $sc.DdlBodyOnly = 1;
            $sc.DriAll = 1;
            $sc.DriAllConstraints = 1;
            $sc.DriAllKeys = 1;
            $sc.DriPrimaryKey = 1;
            $sc.IncludeDatabaseContext = 1;
            $sc.IncludeDatabaseRoleMemberships = 1;
            $sc.IncludeHeaders = 1;
            $sc.IncludeIfNotExists = 1;
            $sc.Indexes = 1;
            $sc.LoginSid = 1;
            $sc.PrimaryObject = 1;
            $sc.Permissions = 1;


 Depending upon your need, you can set the target server version, and the output file properties as well.

Comments (5) -

5/6/2006 10:26:07 AM #


Is it possible to automate the execution of an SSIS package from VB 2008 (or c#) and, also, asynchronously retrieve the status of these (long running) packages so that the user can see where in the process the package is?  If so, where is a good resource/example of such so that I may look further into this.


Mike United States

5/6/2006 6:29:31 PM #


Hello, Mike,

Are you asking about scheduling SSIS job from VB or display progress bar from SSIS pkg?

For the progress bar try this:
- Use a script task component and place in the event handlers for OnProgress event
- add reference to System.Windows.Forms and draw a custom windows and progress bar
- Define package level variables so that you can manage how the front end interacts with the end user

BTW, this blog like blogs are moderated. This is because I get too many spams coming into the blog offering acai berry, drug, travel deal, pay day loans products etc. It takes me a while to review the comments and approve the proper ones.

rajib.bahar United States

5/7/2006 12:52:55 AM #


Hello Rajib,

Thanks for the reply!  Sorry about the comments, yes - the spam is out of control.  :-(  

The user would like to be able to have a VB 2008 (or c#) app and have a drop down list of available import processes to chose from.  These import processes are directly tied to an SSIS package, each, on the SQL Server (2008).  When the choose (example: Import ABC) and click "GO", I would like to know if the program can can execute the package and monitor its steps (the step status) right from the program.   This is so that I do not have to log in and run these packages for them, they can do this themselves.

The packages are fairly long running (some over 1 hour) and the user wants to see where in the process they are with this small app.  If the SSIS import fails, they need to know which step and then I get the call and have to remotely log in and try to correct the data and restart the job at the failed step.

I basically want to be able to hook into the running SSIS package's steps (where is it at, what was the return code, how many records were imported, discarded, etc).  I am exposing some of those variables (# imported, # discarded, etc)..

I have been reading about this since my first comment and have come across SMO?   Is this something you would recommend looking further into?  

After I get that working then I will enhance it to email me and let me know if a package fails, what step, etc so I can be proactive in fixing it before they call me.

If you could point me to an example or site where someone is discussing this, that would be awesome.  I feel like I may not know exactly where to search although I do know what I would like that application to do.


Mike United States

5/7/2006 1:43:37 AM #


I don't have the complete answer to your question. I'll try to answer few parts of it as best as I can.

SSIS packages can be scheduled as jobs. If I was approaching this problem, then I'd look into job schedules to solve this issue. The namespace Microsoft.SqlServer.Management.Smo.Agent has all the Job classes. My instinct would be to do some trial and error with the classes in there. See if Job.CurrentRunStatus or CurrentRunStep help address your issue or not.

Also, have a look at this article:

Good luck!

rajib.bahar United States

5/7/2006 1:49:56 AM #


Thank you for that!  I will read that article and look into SMO more and for the namespace suggestion!


Mike United States

Tag cloud

Month List