What are the SQL Server Management Objects

SQL Server Management Objects (also called SMO) is a .NET library which allows you to access and manage all objects of the Microsoft SQL Server. Such a library also exists for Analysis Services (AMO) and SQL Server Replication (RMO). Why do I write about it? I did a project which used SMO a lot, so it is a sort of brain dump for me. It hope this article will save you the time I spent at the beginning of my project.  

SMO supports SQL Server 2000, 2005 and 2008. The required DLLs are in assembly folder of your SQL Server Version (they are not registered in the GAC) directory. To execute the code samples, you must have a SQL Server installed on your computer, which allows you to login with integrated security. Otherwise, you will have to change server connection information.

The Server Object

The library has the same hierarchical arrangement as you see it in the SQL Server Management Studio. At the top of this hierarchy is the Server object.

private Server Connect()
{
    var connection = new ServerConnection();
    connection.ServerInstance = "localhost";
    connection.LoginSecure = true;
    connection.Connect();
    return new Server(connection);
}

The server object contains all information about the SQL Server, a list with all database objects, a list with all roles, a reference to the Job Server and so on. You get a new Server object with a ServerConnection. The ServerConnection can also be instantiated with a SqlConnection (and also provides more overloads). So the ServerConnection contains similar information that the connection string normally contains.

Database, Tables, Views and other DB Objects

The sample below lists all databases of the server, all tables and views which the databases contain. Don't execute this code on a server which contains a lot of databases, it could take some minutes!

public void PrintDatabases()
{
    var server = Connect();

    foreach (Database db in server.Databases)
    {
        Console.WriteLine("Database: {0}", db.Name);

        foreach (Schema schema in db.Schemas)
        {
            printTables(schema, db);
        }

        printViews(db);
    }

    server.ConnectionContext.Disconnect();
}

private void printTables(Schema schema, Database db)
{
    foreach (Table table in db.Tables)
    {
        if (schema.Name.Equals(table.Schema))
        {
            Console.WriteLine(" Table: {0}.{1}", table.Schema, table.Name);
            printColumns(table.Columns);
        }
    }
}

private void printColumns(ColumnCollection columns)
{
    foreach (Column c in columns)
    {
        Console.WriteLine("  Column: {0}", c.Name);
    }
}

private void printViews(Database db)
{
    foreach (View view in db.Views)
    {
        Console.WriteLine(" View: {0}", view.Name);
        printColumns(view.Columns);
        printIndexes(view.Indexes);
    }
}

Also here you see the hierarchical structure. The table object contains the columns but also the indexes which the table contains. The index of each collection allows to access every object with the name. So if I want to access the table "Contact" with the schema "Person" in the database "AdventureWorks", it would look like this:

public Table GetContact()
{
    return server.Databases["AdventureWorks"].Tables["Contact", "Person"]; 
}

More about SMO

A lot more examples provide MSDN separated in programming specific tasks. There you will also find a great overview of the object model. My next post will show how you can modify objects which live in the SQL Server.

Download the source code here!