.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

What are the Sql Server Management Objects

Posted By:Mattia Baldinger       Posted Date: August 03, 2010    Points: 5    Category: DataBase    URL: http://www.dotnetspark.com  

What are the Sql Server Management Objects? It is a .NET library from Microsoft which allows you to access and manage all objects of the Microsoft SQL Server. It allows you to create, alter and drop objects like views, tables, jobs from your .NET application in a easy way.


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 Dll's 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;
return new Server(connection);

The server object 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 the similar information than the connection string normally contains.

Database, Tables, Views and other DB objects

The sample below lists all all databases of the server, all tables and views which the databases contains. 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);



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);

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);

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 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 provides 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 lives in the SQL Server.

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend