SQL Server Management Objects (SMO)

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.SMO dis this work.

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.

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

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(@".\SQLExpress"); //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();

f