Getting started with SQL Server Management Objects (SMO)

By:   |   Comments (11)   |   Related: > SQL Server Management Objects SMO


Problem

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. In this tip, I am going to discuss how you can get started with SMO and how you can programmatically manage a SQL Server instance with your choice of programming language.

Solution

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. I am going to demonstrate the usage of a few instance classes in this tip in the example section below.
  • 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. I will discussing the utility classes in my next tip.

How SMO is different from SQL-DMO

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.

An exhaustive list of the comparisons between SQL-DMO and SMO can be found here.

Example

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

 Getting hands on SMO Managing SQL Server Programmatically img1

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.

Namespaces Purpose
Microsoft.SqlServer.Management.Common It contains the classes which you will require to make a connection to a SQL Server instance and execute Transact-SQL statements directly.
Microsoft.SqlServer.Management.Smo This is the basic namespace which you will need in all SMO applications, it provides classes for core SMO functionalities. It contains utility classes, instance classes, enumerations, event-handler types, and different exception types.
Microsoft.SqlServer.Management.Smo.Agent  It provides the classes to manage the SQL Server Agent, for example to manage Job, Alerts etc.
Microsoft.SqlServer.Management.Smo.Broker It provides classes to manage Service Broker components using SMO.
Microsoft.SqlServer.Management.Smo.Wmi  It provides classes that represent the SQL Server Windows Management Instrumentation (WMI). With these classes you can start, stop and pause the services of SQL Server, change the protocols and network libraries etc.

C# Code Block 1

Here I am using the Server instance object to connect to a SQL Server. You can specify authentication mode by setting the LoginSecure property. If you set it to "true", windows authentication will be used or if you set it to "false" SQL Server authentication will be used.

With Login and Password properties you can specify the SQL Server login name and password to be used when connecting to a SQL Server instance when using SQL Server authentication.

// C# Code Block 1 - 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";

C# Code Block 2

Once a connection has been established to the server, I am enumerating through the database collection to list all the database on the connected server. Then I am using another instance class Database which represents the AdventureWorks database. Next I am enumerating through the table, stored procedure and user-defined function collections of this database instance to list all these objects. Finally I am using the Table instance class which represents the Employee table in the AdventureWorks database to enumerate and list all properties and corresponding values.

// C# Code Block 2 - retrieving databases, tables, SPs, UDFs 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 function 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);
}

C# Code Block 3

This demonstrates the usage of SMO to perform DDL operations.

First I am checking the existence of a database, if it exists dropping it and then creating it.

Next I am creating a Table instance object, then creating Column instance objects and adding it to the created Table object. With each Column object I am setting some property values.

Finally I am creating an Index instance object to create a primary key on the table and at the end I am calling the create method on the Table object to create the table.

// C# Code Block 3 - 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(); // you can specify constraint name here as well
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 created on the server 
myEmpTable.Create();

Result:

Getting hands on SMO Managing SQL Server Programmatically img2

The complete code listing (created using SQL Server 2008 and Visual Studio 2008, although there is not much difference if you are using SQL Server 2005 and Visual Studio 2005) can be found in the below text box.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
namespace LearnSMO2008
{
    class Program
    {
        static void Main(string[] args)
        {
            Server myServer = new Server(@"ARSHADALI\SQL2008");
            try
            {
                //Using windows authentication
                myServer.ConnectionContext.LoginSecure = true;
                //Using SQL Server authentication
                //myServer.ConnectionContext.LoginSecure = false;
                //myServer.ConnectionContext.Login = "SQLLogin";
                //myServer.ConnectionContext.Password = "entry@2008";
                myServer.ConnectionContext.Connect();
                //AccessingSQLServer(myServer);
                DatabaseObjectCreation(myServer);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (myServer.ConnectionContext.IsOpen)
                    myServer.ConnectionContext.Disconnect();
                Console.ReadKey();
            }
            
        }
        private static void AccessingSQLServer(Server myServer)
        {
            //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 function 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);
            }
        }
        private static void DatabaseObjectCreation(Server myServer)
        {
            //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(); // you can specify constraint name here as well
            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 created on the server            
            myEmpTable.Create();
        }
    }
}

Notes:

  • If you have an application written in SQL-DMO and want to upgrade it to SMO, that is not possible, you will need to rewrite your applications using SMO classes.
  • SMO assemblies are installed automatically when you install Client Tools.
  • Location of assemblies in SQL Server 2005 is C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder.
  • Location of assemblies in SQL Server 2008 is C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
  • 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.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, November 29, 2017 - 5:29:09 AM - Rohit Back To Top (73404)

How we can access the Local Server Groups list in C# (using SMO or without using SMO).

I am creating a Window application, where i need the list of sever group So that I can create or delete the groups from that window application.

Thursday, June 4, 2015 - 4:07:52 PM - Iz Back To Top (37661)

Hello, I'm a newbie.  Thanks so much for sharing.  I've successfully connected, but not sure how to disable/enable a trigger. 

Dim mytbl As Table
mytbl = mydb.Tables("tablename")
Dim tr As Trigger
tr = New Trigger(mytbl, "triggername") 'ERRORS on this line
tr.IsEnabled = False

Appreciate any help.

 

 

 

 

 

 

Thursday, December 19, 2013 - 11:09:57 AM - Michael Back To Top (27848)

This simple tuturial is a basic infomation about the SMO. i so glad i found site and give me more ideas how to go deeper inside SQL server database. i appreciated of this post. i hope you can tell more advance about SMO.

 

i have a question, if  we write a code that will locate the location of the databace?

Example:

 

database  db = new databases()

db.location = "d:\Database\sampledb..." ??

db.loglocation = "d:\Database\sampledb..log" ??

db.create()

Friday, March 8, 2013 - 12:18:21 AM - eskyooel Back To Top (22649)

Was wondering how I can programmatically get performance metrics for a given sql server instance or database by using SMO (sql management objects).

So for example if I want to know things like the CPU usage, I/O writes, etc, which SMO object will give me that?

 

I've tried using the following but I don't think I'm on the right track.

 

Server srv = new Server(conn);

DataRow[] datarows = srv.EnumPerformanceCounters().Select();

Saturday, February 2, 2013 - 2:50:31 AM - Amar Patil Back To Top (21875)

Dear Sir,

 

 DataBase Allready created in this SERVER that time next time same name database created that time how can ?

Any idea, Give me ans. my email.ID :- [email protected]

 

Thanks and Regards

Amar Patil.

 

Friday, December 28, 2012 - 10:33:41 AM - efe Back To Top (21175)

thanks you so much for this article. 

Friday, October 26, 2012 - 2:55:27 AM - Raman Back To Top (20096)

Hi,

I would like to know the list of SQL user rights to be given to use the SMO objects. Could you pls help me on this.

Thanks

Wednesday, August 22, 2012 - 7:40:19 AM - Jamil Akbar Back To Top (19159)

Great post. I am using SQL Server & DMO since a long time. I am using SQL SMO first time to create a routine for database restore but whenever i create object like

Dim sqlRestore as New Restore

word restore become underlined & shows an error saying that restore is not defined. I have added all references to the project as below 

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Microsoft.SqlServer.SqlEnum

 

also used

 

Imports  Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common

 

Important point: when i declare Server object it again shows error and says "Reference Required to assembly 'Microsoft.SqlServer.Management.sdk.sfc'"

after adding that reference server object works well

another point i have noted that all refernces that i have added were found in

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

but i didn't found SqlEnum there

Please help me to fix this problem. am i missing some step or should i download some additional stuff. I am using VS2010, SQLExpress 2008 and SQL Server Management Studio 2008, SQL Server 2008 also installed.  

Tuesday, August 14, 2012 - 7:17:28 AM - Jeff Moden Back To Top (19034)

This is a good post with some nice, simple examples to do things.  However, being more of a data troll rather than I front end guy, I'm still confused why folks would spend so much time writing code to do what SQL Server can easily generate scripts for.

I also realize that these are simple examples but I'm concerned about code examples like the following...

//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();

If a 3rd party application were to drop one of my databases because I just happened to use the same name for the database, I'd be livid even though I could easily restore it.  The code really should have a check to STOP running if the database already exists or at least ask the question if it should be dropped.

The other thing that I'm concerned with is that the default "growth" settings on most SQL Servers cause huge amounts of both database and operating system fragmentation of databases.  It would be nice if you would modify that snippet of code to include such settings and possibly a comment of "See your DBA for what the correct settings should be". 

The reason for so much concern is that you are a well known and trusted individual with many excellent tips and I'm concerned that front end developers will take your code exactly as it's written and disaster could ensue.

As a side bar and as you pointed out, lot's of people spent a lot of time writing SQL DMO and it all had to be rewritten to use SQL SMO.  With that thought in mind, I wonder what the future of SQL SMO holds for us.  Will it, too, become hopelessly obsolete?  It's not a question that I need to have answered.  It's a rhetorical question to raise awareness.

 

 

 

Tuesday, April 5, 2011 - 11:44:56 AM - Bill Back To Top (13438)

Thanks! That helped alot.

I made use of the drop and create index scripts :] - it worked great.

Monday, March 22, 2010 - 10:56:52 AM - phillips_jim Back To Top (5094)

Would like to seen some examples using PowerShell instead of C#.  As a DBA I would be more likely to use PowerShell than C# when using SMO.

Can you present a followup tip showing some PowerShell examples?














get free sql tips
agree to terms