SQL Server Management Objects

In a project of mine, we had to move a lot of database (db) objects like tables, views, triggers, etc. from our local server to a development server. We were creating a huge number of database objects on a regular basis and many a time some or other object was not moved.

So, I—smart person that I am—decided to write a tool for doing this job. (I know there are many tools already existing, but I wanted to do this myself and to learn a few things in the process.) This led me down a path where I discovered SQL Server Management Objects (SMO) which are available with SQL Server 2005 and allow me to get the script of various database objects.

Scripting Database Objects with SMO

The intent of this article is to show what SMO is and to discuss some of the new features in SMO.

For transferring your database objects to a different server or to recreate them on a different server programmatically, the scripting of objects is required. It is possible to query the System Tables for fetching the script or using system stored procedures is also a possibility. (However, sp_help does not return the "Create" script for a table.)

SMO is a .NET assembly included in .NET 2.0: It is not a COM object. Hence, it is possible to generate scripts for your database objects, including Create Table scripts in a managed environment.

Let's first talk about what SMO is. SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of Microsoft SQL Server management. SMO contains a vastly improved programming and object model to provide a more robust programming experience.

The list of features include the following:

• Improved performance.

• Support for the XML data type.

• Advanced scripting.

• HTTP endpoint support.

• Service broker support.

• Synonym support for object names.

• Support for Notification Services.

• Snapshot isolation

• The ability to trace and replay SQL Server events.

• Improved database object coverage.

The new SMO functionality is provided by the different namespaces that make up the SMO object model and programming model. Each namespace provides a specific functionality within SMO. Those namespaces and the functionality they provide are listed in the following table:

Unless you are doing something with WMI (Windows Management Instrumentation), SQL Server Service Broker, or Notification Services, the Microsoft.SqlServer.Management.Smo namespace will provide most of the functionality you need. Connections to SQL Server are handled via the Microsoft.SqlServer.Management.Common namespace, as this namespace contains all the necessary classes used to establish and maintain connections to SQL Server instances and their connection settings.

The Microsoft.SqlServer.Management.Smo namespace provides the ability to view and modify SQL Server options and settings, to view and modify SQL Server engine objects, to backup and restore databases (via access to databases and database backup devices), as well as to provide access to all DDL (Data Definition Language) tasks. This namespace also lets you view and script database dependencies.

Getting back to the job at hand, shown below is an example of getting a "Create Script" of a table in your database using the Scripter object of SMO. Scripter is the over-all, top-level object for managing scripting operations.

The Scripter object allows programmatic access to scripting settings and is responsible for the following scripting functionality:

• Finding dependencies.

• Output script to memory and file.

• Calling the instance objects to be scripted.

• Managing context of a script operation, such as checking whether an object has already been scripted.

The Scripter object has a Script method which returns the script for the SMO Object. The Script method expects a SqlSmoObject array.

//Establish the database connection 
 
String connectionString  = "….";
SqlConnection connection = new SqlConnection( connectionString );
Server server = new server( connection );
 
//Access the database on your server
 
Database testDB =server.Databases["testdb"];
Table testTable = testDB.Tables["testtable"];
 
//Declare a String Collection Object to store the Script.
 
System.Collections.Specialized.StringCollection strCollection 
   = new System.Collections.Specialized.StringCollection();
 
// Declaring array of SqlSmoObject. 
 
// The SqlSmoObject object represents all Microsoft SQL Server objects.
 
SqlSmoObject[] smoObj = new SqlSmoObject[1];
smoObj[0] = testTable;
 
//Declare Scripter object for the server. 
 
Scripter scriptor = new Scripter(server);
strCollection  = Scriptor.Script( smoObj );

It is, also, possible to script the table without creating the Scripter object.

Table testTable = testDB.Tables["testtable"];
StringCollection strCollection = new StringCollection();
strCollection = testTable.Script();

The advantage of using the Scripter class is that apart from scripting database objects, it is, also, possible to use it to script INDEXs, IDENTITY, IF NOT Exists clauses. The following is an example:

Scripter scriptor = new Scripter(server);
scriptor.Options.IncludeIfNotExists = true;	
scriptor.Options.NoIdentities = true;

The same (preceding) steps can be used for views, triggers, stored procedures, user-defined functions, and so on.

Conclusion

SQL Server Management Objects is a cool feature of SQL Server 2005 that allows you to create database objects—tables, stored procedures, etc.—programmatically using C# or Visual Basic .NET. Using various classes provided by SMO, it is possible to transfer, backup, and recover the database objects in a simple and efficient manner.

The examples in this article don’t even begin to show how broad SMO really is, but hopefully you can begin to get a sense of how significant it is.