Click here to Skip to main content
13,405,744 members (56,324 online)
Click here to Skip to main content
Add your own
alternative version


32 bookmarked
Posted 1 Feb 2008

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


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

	// 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");
				"IsSystemObject", "CreateDate");
				"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;
    foreach (Database db in serv.Databases) //iterate through the database collection
	if (!db.IsSystemObject)
    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()
	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;
	using (StreamReader sr = new StreamReader(_filename))
	   String line;
	   string formatLine;
	   int i = 0;
	   while ((line = sr.ReadLine()) != null)
	       if (line.Trim().Length > 0)
	            formatLine = string.Format("{0}\t{1}\r\n", i, line);
	            _objectDef += formatLine;
        if (File.Exists(@"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.


  • 1st February, 2008: Initial post


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


About the Author

Publisher Ashfield Consultants Ltd
United Kingdom United Kingdom
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionHow can I script the database objects fast? Pin
Hua Yujun2-Apr-13 23:56
memberHua Yujun2-Apr-13 23:56 
GeneralWon't work on x64 machines without extra download Pin
JasonShort25-Jul-09 19:20
memberJasonShort25-Jul-09 19:20 
GeneralRe: Won't work on x64 machines without extra download Pin
Ashfield26-Jul-09 8:33
mvpAshfield26-Jul-09 8:33 
GeneralMy vote of 2 Pin
dojohansen20-Feb-09 4:45
memberdojohansen20-Feb-09 4:45 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180221.1 | Last Updated 1 Feb 2008
Article Copyright 2008 by Ashfield
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid