Using the SqlServer.Management.Smo

• Download source - 22.7 KB

Introduction

There seem to be a lot of questions on the forums about getting details of the various objects in SQL Server. Using .NET this became significantly easier using the Microsoft.SqlServer.Management.Smo (SMO), which allows all sorts of enquires on the database objects.

Using the Code

The code sample shows how to get the table, stored procedure, functions and views from 2 databases and perform a comparison. It's not a particularly good comparison, the idea of the application is to show some of the uses of SMO. The application is not meant to be production strength code.

When you enter a server name and click Connect, it creates a new SMO Server object and attempts to connect. By default, it will use a trusted connection. It uses the database collection of the Server object to populate the combobox of Databases. System databases (such as master) are excluded by checking the IsSystemObject property.

private Server InitialiseServer(string ServerName, ComboBox cb)
{
    Server serv = new Server(ServerName);   //Create SMO Server object

    try
    {
	// set the default properties we want upon partial instantiation - 
	// smo is *really* slow if you don't do this
	serv.SetDefaultInitFields(typeof(Table), "IsSystemObject", "CreateDate");
	serv.SetDefaultInitFields(typeof(StoredProcedure), 
				"IsSystemObject", "CreateDate");
	serv.SetDefaultInitFields(typeof(UserDefinedFunction), 
				"IsSystemObject", "CreateDate", "FunctionType");
	serv.SetDefaultInitFields(typeof(Smo.View), "IsSystemObject", "CreateDate");
	serv.SetDefaultInitFields(typeof(Column), "Identity");
	serv.SetDefaultInitFields(typeof(Index), "IndexKeyType");
    }
    catch (Exception ex)
    {
	MessageBox.Show(ex.Message);    //Probably failed to connect
	return null;
    }
    cb.Items.Clear();
    foreach (Database db in serv.Databases) //iterate through the database collection
    {
	if (!db.IsSystemObject)
	{
	   cb.Items.Add(db.Name);
	}
    }
    cb.Enabled = true;
    return serv;
}

Once both databases are selected and the Compare button is clicked, the required tables/stored procedures/views/functions are loaded from the relevant collection of the selected databases by iterating through the appropriate collection of the SMO Database object - again system objects are excluded by checking IsSystemObject.

//excerpt from method...

AddToDictionary(_server1Tables, _server1Database.Tables);
AddToDictionary(_server2Tables, _server2Database.Tables);

//end excerpt

private void AddToDictionary(Dictionary Dict, SchemaCollectionBase collection)
{
    foreach (NamedSmoObject smoObj in collection)
    {
	tsProgressbar.Value += 1;
	if (!IsSystemObject(smoObj))
	{
	   Dict.Add(smoObj.Name, new DBObject(smoObj));
	}
    }
}

This is where the interesting bit comes in. Each of the objects supports the Microsoft.SqlServer.Management.Smo IScriptable class, which is used to generate the T-SQL script for the object. By setting the relevant options in the ScriptingOptions object, include or exclude the various options to suit your purpose. These include scripting out the indexes, triggers permission, etc. You can script to a StringCollection (System.Collections.Specialized.StringCollection), but the result is not very well formatted for reading, so I script to a file and then read it back in.

private void ScriptObject()
{
    try
    {
	string _filename = @"out.txt";
	_objectDef = string.Empty;
	ScriptingOptions options = new ScriptingOptions();
	options.Encoding = Encoding.Default;
	options.ScriptDrops = false;
	options.IncludeDatabaseContext = false;
	options.IncludeIfNotExists = false;
	options.FileName = _filename;

	options.ClusteredIndexes = true;
	options.ExtendedProperties = true;
	options.Indexes = true;
	options.NonClusteredIndexes = true;
	options.Permissions = true;
	options.Triggers = true;
	options.DriAll = true;
	IScriptable me = (IScriptable)_smoObject;
	me.Script(options);
	using (StreamReader sr = new StreamReader(_filename))
	{
	   String line;
	   string formatLine;
	   int i = 0;
	   while ((line = sr.ReadLine()) != null)
	   {
	       if (line.Trim().Length > 0)
	       {
	            i++;
	            formatLine = string.Format("{0}\t{1}\r\n", i, line);
	            _objectDef += formatLine;
	            _arrayDef.Add(formatLine);
                }
            }
        }
    }
    finally
    {
        if (File.Exists(@"out.txt"))
        {  
            File.Delete(@"out.txt");
        }
    }
} 

Most of the ScriptingOptions are self-explanatory, perhaps with the exception of DriAll. Setting this to true scripts out all the enforced Declared Referential Integrity options such as Foreign Key Constraints, Defaults etc.

For more about SMO, visit the Microsoft Technet Site.

Please note that this code is supplied as an example only.

History

• 1^st February, 2008: Initial post