Scripting Database Objects using SMO (Updated)

By Bill Graziano on 29 November 2005 | Tags: Administration , SMO

With the introduction of SQL Server 2005, Microsoft has created a new .NET management API for SQL Server called SQL Management Objects (SMO). As I started working with SQL Server in earnest following its release I discovered a few limitations that I hoped to correct using SMO. This article describes those problems and how to use SMO to script database objects. UPDATE: Euan's post in the comments for this article pointed me a to a feature of SMO that dramatically increases the performance of enumerating objects in the database. I've updated the article and my sample code accordingly.

I've finally started working with SQL Server 2005 on a regular basis. Now that I'm forced to use it for my every day tasks I'm finding a few interesting things. One of them involves scripting in Management Studio. It seems you can't generate DROPs and CREATEs in the same script file. There's a bug filed in the MSDN Product Feedback Tool if you'd like to vote on the priority of this change. You also can't generate one script file per object. I thought it would be pretty easy to whip out a little application to do those things. Here's what I learned along the way.

Sample code related to this article can be downloaded in a Windows application. This is a .NET 2.0 application and requires that the SQL Server client tools are installed. My discussion assumes you're creating a .NET application using Visual Studio 2005. I use C# for the code in the article. You can find SMO information in Books Online at SQL Server 2005 Books Online -> SQL Server Programming Reference -> Database Engine Administration Programming -> SQL Management Objects (SMO).

Note: I've released my application to script objects with drops for SQL Server 2005 as a ClickOnce application. It installs directly from web site. I'll be releasing the source code once I've done a little more cleanup on it.

Connecting to the Server

The first step is to add the project references to the SMO DLL's. These can be found in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies directory. You'll need to add references to the following DLL's:

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll

And one of the coolest features of Visual Studio 2005 is that it remembers my recent referenced DLL's. You'll also need to add a couple of using statements.

using Microsoft.SqlServer.Management.Common; 
using Microsoft.SqlServer.Management.Smo;

The code to connect to the server and list out a few basic properties looks like this:

Server theServer = new Server("myServerName"); 
Console.WriteLine(theServer.Information.VersionString);

The prints out 9.00.1399.06 on my computer. The Server class assumes a trusted connection unless you tell it otherwise. There are properties for such things as the databases, backup devices, full-text indexes, linked servers, logins, mail, roles, triggers, etc.

Connecting to a Database

One of the properties of the Server class is Databases. This property returns a DatabaseCollection that you can iterate through to find out about the databases on the server.

Server theServer = new Server("myServerName");
foreach (Database db in theServer.Databases) 
{
    Console.WriteLine(db.Name + " System Database: " + db.IsSystemObject.ToString()); 
}

The Database class has properties for Name, Tables, Collation, Roles, Size, etc. There are literally dozens of additional properties. We'll start by looking the properties for stored procedures since that's what I want to script.

The foreach statement allows us to iterate through items in a collection. I won't go into the details of how collections are defined and used in this article. Just know that a collection allows you to store a group of (usually) like objects together. The Databases property of the Server class returns an object with a type of DatabaseCollection. The DatabaseCollection type (or class) is a custom collection that is part of SMO. It contains a group of Database objects. The foreach statement allows us to access each object (of type Database) in the collection one at a time.

We can also reference an object in the collection directly using an indexer. For example if we wanted the first database in the collection we can access it like this:

Database myDB = theServer.Databases[0];

Each collection defines its own specfic indexers. The DatabaseCollection also allows us to use the database name as an index.

Database myDB = theServer.Databases["tempdb"];

Looking at Objects

We can iterate through the stored procedures the same way we iterated through the databases. There's a StoredProcedures property that returns a StoredProcedureCollection That code looks like this:

Server theServer = new Server("myServerName"); 
Database myDB = theServer.Databases["myDatabaseName"]; 

foreach (StoredProcedure sp in myDB.StoredProcedures) 
{ 
     Console.WriteLine(sp.Name); 
}

If we want to limit our results to exclude the system stored procedures we can modify our loop to look like this:

Server theServer = new Server("myServerName"); 
Database myDB = theServer.Databases["myDatabaseName"]; 

foreach (StoredProcedure sp in myDB.StoredProcedures) 
{ 
    if ( sp.IsSystemObject == false )
        Console.WriteLine(sp.Name); 
}

My initial test of this code concluded that this performed very poorly. After reviewing Euan's post in the comments for this article I tried the following code:

Server theServer = new Server("myServerName"); 
theServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

Database myDB = theServer.Databases["myDatabaseName"]; 

foreach (StoredProcedure sp in myDB.StoredProcedures) 
{ 
     Console.WriteLine(sp.Name); 
}

By default SMO doesn't return every property for every object when you reference a collection such as the StoredProcedures collection. The IsSystemObject property is one that isn't returned by default. The SetDefaultInitFields method tells the SMO API to add the IsSystemObject property to the default list for this session. In my testing this method call had be issued before any other work was done with the server. After adding this line to my sample the performance was great.

So what's another way to list all the user stored procedures in SMO? And how do we exclude the system stored procedures? There's a method for the Database class called EnumObjects that returns a DataTable object. We can also filter the result by object type. Using this to get the stored procedures looks like this:

Server theServer = new Server("myServerName");
Database myDB = theServer.Databases["myDatabaseName"];

DataTable dt = myDB.EnumObjects(DatabaseObjectTypes.StoredProcedure);

If we bind our DataTable to a DataGridView we'll see a result set that looks like this:

DatabaseObjectType  Schema  Name             Urn
------------------  ------  -----------      -----------------------------
StoredProcedure     dbo     OrgType_Get      Server[@Name='L30\SQL2K5']/Database[@Name='TAMSDev']/Store...
StoredProcedure     dbo     OrgType_GetList  Server[@Name='L30\SQL2K5']/Database[@Name='TAMSDev']/Store...
. . .
StoredProcedure     sys     sp_dboption      Server[@Name='L30\SQL2K5']/Database[@Name='TAMSDev']/Store...
StoredProcedure     sys     sp_databases     Server[@Name='L30\SQL2K5']/Database[@Name='TAMSDev']/Store...

All the system stored procedures are in the sys schema so now I can easily exclude those. So we're finally up to the actual scripting.

Scripting Objects

We'll use the Script method of the StoredProcedure class to generate the actual script. Each of the classes for the other object types also includes a Script method.

Server theServer = new Server("myServerName");
Database myDB = theServer.Databases["myDatabaseName"];

StoredProcedure sp = myDB.StoredProcedures["mySprocName", "dbo"];

StringCollection sc = sp.Script();

foreach (string s in sc)
  Console.WriteLine(s);

The Script method returns a StringCollection object. (Note: The StringCollection option is in the System.Collections.Specialized class so be sure to include a using statement for it.) We can iterate through this to capture our scripts. We can also set some options for the scripting. This is how we script drops. Scripting a drop looks like this:

Server theServer = new Server("myServerName");
Database myDB = theServer.Databases["myDatabaseName"];

StoredProcedure sp = myDB.StoredProcedures["mySprocName", "dbo"];

ScriptingOptions so = new ScriptingOptions();
so.ScriptDrops = true;
so.IncludeIfNotExists = true;

StringCollection sc = sp.Script(so);

foreach (string s in sc)
  Console.WriteLine(s);

This scripts a DROP instead of a CREATE. It also includes an IF EXISTS in the script before it tries to drop the object. After exploring the SMO API you'll notice that there's no way to generate a DROP and a CREATE in the same script. You need to call the script method twice with different options. There's also a Script method for the Database class that takes an array of objects as a parameter. If I get a chance I'll explore that in a future article.

Summary

So there's my little journey through the SMO API trying to script objects. Download the sample application and do some exploring. IntelliSense in Visual Studio 2005 is a great way to explore the API.