Click here to Skip to main content
Click here to Skip to main content

Using the SqlServer.Management.Smo

, 1 Feb 2008
Rate this:
Please Sign up or sign in to vote.
Making use of the SqlServer.Management.Smo to get information about database objects

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<string, /> 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

  • 1st February, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Ashfield
Publisher Ashfield Consultants Ltd
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
QuestionHow can I script the database objects fast? PinmemberHua Yujun2-Apr-13 22:56 
GeneralWon't work on x64 machines without extra download PinmemberJasonShort25-Jul-09 18:20 
GeneralRe: Won't work on x64 machines without extra download PinmvpAshfield26-Jul-09 7:33 
GeneralMy vote of 2 Pinmemberdojohansen20-Feb-09 3:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 1 Feb 2008
Article Copyright 2008 by Ashfield
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid