SQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO) which was used for SQL Server 2000.

Although SQL Server Management Studio (SSMS) is a great tool to manage a SQL Server instance, there might be a need to manage your SQL Server instance programmatically. For example, consider you are developing a build deployment tool, this tool will deploy the build but before that it needs to make sure that the SQL Server and SQL Server Agent services are running, a database is available and online. For this kind of work, you can use SMO, a SQL Server API object model.

The SMO object model represents SQL Server as a hierarchy of objects. On top of this hierarchy is the Server object, beneath it resides all the instance classes. SMO classes can be categorized into two categories:

• Instance classes – SQL Server objects are represented by instance classes. It forms a hierarchy that resembles the database server object hierarchy. On top of this hierarchy is Server and under this there is a hierarchy of instance objects that include: databases, tables, columns, triggers, indexes, user-defined functions, stored procedures etc.
• Utility classes – Utility classes are independent of the SQL Server instance and perform specific tasks. These classes have been grouped on the basis of its functionalities. For example Database scripting operations, Backup and restore databases, Transfer schema and data to another database etc.

SMO object model is based on managed code and implemented as .NET Framework assemblies. It provides several benefits over traditional SQL-DMO along with support for new features introduced with SQL Server 2005 and SQL Server 2008. For example:

• It offers improved performance by loading an object only when it is referenced, even the objects properties are loaded partially on object creation and left over objects are loaded only when they are directly referenced.
• It groups the T-SQL statements into batches to improve network performance.
• It now supports several new features like table and index partitioning, Service Broker, DDL triggers, Snapshot Isolation and row versioning, Policy-based management etc.

Before you start writing your code using SMO, you need to take reference of several assemblies which contain different namespaces to work with SMO. To add a reference of these assemblies, go to Solution Browser -> References -> Add Reference. Add these commonly used assemblies:

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SmoEnum.dll
• Microsoft.SqlServer.SqlEnum.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll (on SQL Server/VS 2008 only)

There are a couple of other assemblies which contain namespaces for certain tasks, but few of them are essential to work with SMO. Some of the frequently used namespaces and their purposes are summarized in the below table, other namespaces are used for specific tasks like working with SQL Server Agent where you would reference Microsoft.SqlServer.Management.Smo.Agent etc.

Here is an example of connecting to a SQL Server instance using SMO:

// Connecting to server 
Server myServer = new Server(@"ARSHADALI\SQL2008");

// Using windows authentication
myServer.ConnectionContext.LoginSecure = true;
myServer.ConnectionContext.Connect();

// Do your work

if (myServer.ConnectionContext.IsOpen)
    myServer.ConnectionContext.Disconnect();

// Using SQL Server authentication
myServer.ConnectionContext.LoginSecure = false;
myServer.ConnectionContext.Login = "SQLLogin";
myServer.ConnectionContext.Password = "entry@2008";

Once a connection has been established to the server, you can perform various operations using SMO. Here is an example of retrieving databases, tables, stored procedures, user-defined functions, and properties:

// List down all the databases on the server
foreach (Database myDatabase in myServer.Databases)
{
    Console.WriteLine(myDatabase.Name);
}

Database myAdventureWorks = myServer.Databases["AdventureWorks"];

// List down all the tables of AdventureWorks
foreach (Table myTable in myAdventureWorks.Tables)
{
    Console.WriteLine(myTable.Name);
}

// List down all the stored procedures of AdventureWorks
foreach (StoredProcedure myStoredProcedure in myAdventureWorks.StoredProcedures)
{
    Console.WriteLine(myStoredProcedure.Name);
}

// List down all the user-defined functions of AdventureWorks
foreach (UserDefinedFunction myUserDefinedFunction in myAdventureWorks.UserDefinedFunctions)
{
    Console.WriteLine(myUserDefinedFunction.Name);
}

// List down all the properties and its values of [HumanResources].[Employee] table
foreach (Property myTableProperty in myServer.Databases["AdventureWorks"].Tables["Employee", "HumanResources"].Properties)
{
    Console.WriteLine(myTableProperty.Name + " : " + myTableProperty.Value);
}

You can also use SMO to perform DDL operations. Here is an example of creating a database and table:

// Drop the database if it exists
if (myServer.Databases["MyNewDatabase"] != null)
    myServer.Databases["MyNewDatabase"].Drop();

// Create database called "MyNewDatabase"
Database myDatabase = new Database(myServer, "MyNewDatabase");
myDatabase.Create();

// Create a table instance
Table myEmpTable = new Table(myDatabase, "MyEmpTable");

// Add [EmpID] column to created table instance
Column empID = new Column(myEmpTable, "EmpID", DataType.Int);
empID.Identity = true;
myEmpTable.Columns.Add(empID);

// Add another column [EmpName] to created table instance
Column empName = new Column(myEmpTable, "EmpName", DataType.VarChar(200));
empName.Nullable = true;
myEmpTable.Columns.Add(empName);

// Add third column [DOJ] to created table instance with default constraint
Column DOJ = new Column(myEmpTable, "DOJ", DataType.DateTime);
DOJ.AddDefaultConstraint();
DOJ.DefaultConstraint.Text = "GETDATE()";
myEmpTable.Columns.Add(DOJ);

// Add primary key index to the table
Index primaryKeyIndex = new Index(myEmpTable, "PK_MyEmpTable");
primaryKeyIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
primaryKeyIndex.IndexedColumns.Add(new IndexedColumn(primaryKeyIndex, "EmpID"));
myEmpTable.Indexes.Add(primaryKeyIndex);

// Unless you call create method, table will not be created on the server
myEmpTable.Create();

These are just a few examples of what you can do with SMO. It provides a powerful and flexible way to programmatically manage your SQL Server instances. With SMO, you can automate various tasks, perform DDL operations, and retrieve information about databases, tables, stored procedures, and more.

SMO assemblies are installed automatically when you install Client Tools. The location of assemblies in SQL Server 2005 is C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder, and in SQL Server 2008 is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.

It’s important to note that if you have an application written in SQL-DMO and want to upgrade it to SMO, you will need to rewrite your applications using SMO classes. SMO provides support for SQL Server 2000 (if you are using SQL Server 2005 SMO it supports SQL Server 7.0 as well), but a few namespaces and classes are not supported in prior versions.