An introduction to SQL Server Management Objects

Published on 1/2/2007

Introduction

SQL Server Management Objects (SMO) is a collection of objects that allow you to work with SQL Server 2005 databases in a managed environment. Previous to SQL Server 2005 you could use SQL Server Distributed Management Objects (DMO) to work with SQL Server 6.0, 6.5, 7 and 2000 (including MSDE) servers. For the most part, the objects provided with SMO will also work with SQL Server 2000, only those features specific to SQL Server 2005 will not work against a 2000 server.

This article aims to introduce a few of the basic operations that can be performed using SMO such as connecting to an SQL Server, managing databases, tables and stored procedures and performing backup and restore operations against an SQL Server.

Finding and connecting to an SQL Server

Before we can perform any operations against an SQL Server we must first connect to an instance of an SQL Server. To do this, we can either connect directly to a known instance or we can browse for all available SQL Servers that are on the network. Fortunately, retrieving a list of available SQL Servers is an extremely simple process; however, we must first make a reference to the Microsoft.SQLServer.SMO, Microsoft.SQLServer.ConnectionInfo and Microsoft.SQLServer.SqlEnum libraries and add the following Imports statement to our code file:

1 Imports Microsoft.SqlServer.Management.Smo

2 Imports Microsoft.SqlServer.Management.Common

To retrieve a list of available SQL Servers we can use the EnumAvailableSqlServers shared method of the SMOApplication object:

1 Dim availableServers As DataTable = SmoApplication.EnumAvailableSqlServers

This method has two overloads, the first allows you to specify whether you want to enumerate through all servers on the network or only the local instance on your machine and the second allows you to specify the name of a computer where the instances of SQL Server are installed. An interesting point to note is that if you are not connected to a network then the method will simply look for the local instance of SQL Server regardless of the overloads you have specified. For demonstration purposes, this article will assume that you are connected to your local instance, although I will show you the code required to connect to any instance of SQL Server that you have access too.

Once this method call has finished, you will be returned a DataTable containing a list of all SQL Server instances found. Note, that if you are connecting to a local instance only then the DataTable will be empty as it does not return details for the local instance. The DataTable contains a column called Name which you can use when specifying the name of the SQL Server that you want to connect to.

To connect to the SQL Server of interest you need to create a server object and specify which SQL Server you are connecting to and how you wish to connect (SQL Server or Windows Authentication). The following code snippet demonstrates how to connect to a local instance of SQL Server using SQL Server authentication:

1 Dim sqlServer As New Server()

3 With sqlServer.ConnectionContext

4 .ServerInstance = "(local)"

5 .LoginSecure = False

6 .Login = "username"

7 .Password = "password"

8 .Connect()

9 .Disconnect()

10 End With

If you wanted to connect to a different SQL Server instance then modify the ServerInstance property to the name of the SQL Server instance you want to connect too.

In the above example, the LoginSecure property is set to False to indicate that SQL Server authentication is to be used. By default, this property is set to True so if you wanted to use Windows Authentication to connect to the SQL Server instance then you can simply remove the LoginSecure, Login and Password properties:

1 Dim sqlServer As New Server()

3 With sqlServer.ConnectionContext

4 .ServerInstance = "(local)"

5 .Connect()

6 .Disconnect()

7 End With

Enumerating and managing databases

Now that we know how to connect to an instance of SQL Server we will probably want to do something useful. To start with, we may want to know what databases exist on the SQL Server we are connected to. To do this, we can enumerate the database collection of the Server object that we used when connecting to the SQL Server instance:

1 For Each db As Database In sqlServer.Databases

2 databasesListBox.Items.Add(db.Name)

3 Next

The above code snippet adds the name of each database to a list box. I recommend that you spend some time looking at the various properties and methods of the database object as there are a number of things that you can ascertain about each database and operations that you can perform. For example, you can query the owner and size of the database by looking at the Owner and Size properties respectively and you can shrink a database by calling the Shrink method.

Creating new databases and attaching existing databases

To create a database you need to create a new Database object and call it’s Create method:

1 Dim newDatabase As New Database(sqlServer, "MyNewDatabase")

2 newDatabase.Create()

Note that the constructor for the Database object requires that you pass the server object that is currently connected to the instance of the SQL Server and the name of the new database.

If you want to specify the owner of the database then you can do this once the database has been created by calling the SetOwner method and passing the name of the owner:

1 newDatabase.SetOwner("OwnerName")

To attach an existing database file to the connected instance of SQL Server then you can use the AttachDatabase method of the Server object:

1 Dim files As New Collections.Specialized.StringCollection

2 files.Add("PathToDatabase.mdf")

3 sqlServer.AttachDatabase("MyNewDatabase", files)

Dropping and detaching existing databases

To drop an existing database you call the Drop method of the Database object that you wish to delete:

1 sqlServer.Databases("MyNewDatabase").Drop()

Alternatively, you can detach a database by calling the DetachDatabase method:

1 sqlServer.DetachDatabase("MyNewDatabase", False)

The DetachDatabase method has a couple of overloads allowing you to specify whether statistics for the database are updated and also to specify whether the full text index file should be removed or not prior to the database being detached.

Enumerating database tables, views and stored procedures

To enumerate all tables within a database, you need to enumerate the Tables collection of a Database object. The following example will add all table names to a list box:

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

3 For Each tb As Table In db.Tables

4 tablesListBox.Items.Add(tb.Name)

5 Next

You can also retrieve the columns of a table by enumerating the table’s Columns collection:

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

2 Dim tb As Table = db.Tables("TableName")

4 For Each col As Column In tb.Columns

5 columnsListBox.Items.Add(col.Name)

6 Next

The code for enumerating Views and Stored Procedures is almost identical to the tables enumeration code above; the only difference is the collection that is enumerated.

Views:

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

3 For Each view As View In db.Views

4 viewsListBox.Items.Add(view.Name)

5 Next

Stored Procedures:

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

3 For Each sp As StoredProcedure In db.StoredProcedures

4 storedProcedureListBox.Items.Add(sp.Name)

5 Next

One point worth noting is that the enumerations above will include system objects. If you do not wish to view these system objects then you can query the IsSystemObject property of the table, view or stored procedure.

Creating and dropping tables

Creating a new table involves creating a Table object and adding new columns to the Table’s Columns collection. Once this is done, you can then call the table’s Create method to create the table on the specified database. The following example creates a basic table containing two columns (IDField and TextField):

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

2 Dim tb As New Table(db, "MyNewTable")

4 Dim newColumn As New Column(tb, "IDField", DataType.Int)

5 tb.Columns.Add(newColumn)

7 newColumn = New Column(tb, "TextField", DataType.Char(25))

8 tb.Columns.Add(newColumn)

10 tb.Create()

However, the code above does not specify whether the IDField is indexed nor does it specify if the IDField should be used as an Identity column. To do this, we first need to create the indexes and state that the IDField column will be used as an Identity column. The following is the above code with the added statements for creating the index:

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

2 Dim tb As New Table(db, "MyNewTable")

4 Dim newColumn As New Column(tb, "IDField", DataType.Int)

5 tb.Columns.Add(newColumn)

7 newColumn.Identity = True

8 newColumn.IdentitySeed = 1

9 newColumn.IdentityIncrement = 1

11 Dim pkIndex As New Index(tb, "IDFieldIndex")

12 tb.Indexes.Add(pkIndex)

13 pkIndex.IndexedColumns.Add(New IndexedColumn(pkIndex, newColumn.Name))

14 pkIndex.IsUnique = True

15 pkIndex.IndexKeyType = IndexKeyType.DriPrimaryKey

17 newColumn = New Column(tb, "TextField", DataType.Char(25))

18 tb.Columns.Add(newColumn)

20 tb.Create()

To drop a table you simply call the Drop method of the table object you wish to delete:

1 sqlServer.Databases("MyNewDatabase").Tables("MyNewTable").Drop()

Creating and dropping stored procedures

To create a stored procedure, you need to create a StoredProcedure object and specify what parameters and command text the stored procedure will consist of. The following code snippet creates a very simple stored procedure that will add a new record to the table that we created earlier:

1 Dim db As Database = sqlServer.Databases("MyNewDatabase")

2 Dim sp As New StoredProcedure(db, "MyNewStoredProcedure")

4 sp.TextMode = False

5 sp.TextBody = "INSERT INTO MyNewTable (TextField) VALUES ('Hello World')"

6 sp.Create()

You can of course create more complex stored procedures. For example we may want to create a similar stored procedure as above but instead of hard coding the value to be inserted into the table we want to define a parameter to be used instead. To do this, we need to create a new StoredProcedureParameter object and add it to the Parameters collection of the new StoredProcedure object:

    1             Dim db As Database = sqlServer.Databases("MyNewDatabase")
    2             Dim sp As New StoredProcedure(db, "MyNewStoredProcedure")
    3 
    4             sp.TextMode = False
    5             sp.Parameters.Add(New StoredProcedureParameter(sp, "@TextFieldValue", DataType.Char(25)))
    6             sp.TextBody = "INSERT INTO MyNewTable (TextField) VALUES (@TextFieldValue)"
    7 
    8             sp.Create()
 
Dropping Stored Procedures
 
To drop a stored procedure you call the Drop method of the StoredProcedure object you wish to delete:

1 sqlServer.Databases("MyNewDatabase").StoredProcedures("MyNewStoredProcedure").Drop()

Backup and restore databases

Backing up a database involves choosing what to backup and to which device you want to store the data. For the sake of simplicity, this section shows you how to backup a database to a file on disk, however there are tools available within the SMO that allow you to query what backup devices are available and also to choose what objects you would like to backup.

To backup an entire database to a disk file involves creating a backup object and specifying what device and database you are going to backup. The following example will backup our new database to a temp folder. Note however, that if you do not specify a location then the backup file will reside in the default Backup directory of SQL Server:

1 Dim backup As New Backup

2 backup.Action = BackupActionType.Database

3 backup.Database = "MyNewDatabase"

4 backup.Devices.AddDevice("C:\Temp\MyNewDatabaseBackup.bak", DeviceType.File)

5 backup.SqlBackup(sqlServer)

Restoring a database is very similar to backing up a database:

1 Dim restore As New Restore

2 restore.Action = RestoreActionType.Database

3 restore.Database = "MyNewDatabase"

4 restore.Devices.AddDevice("C:\Temp\MyNewDatabaseBackup.bak", DeviceType.File)

5 restore.SqlRestore(sqlServer)

Conclusion

As you can see from the few examples provided in this article, the SQL Server Management Objects libraries provide you with a number of tools for performing many powerful operations against an SQL Server. I highly recommend that you play around with these samples and look at the many different options that are available to you throughout these libraries, I am sure you will find that there are many things that you can do with surprisingly little code.