2007
07.17

Traversing Database Server using SQL SMO

SQL SMO (SQL Management Object) is a collection of assemblies that shipped together with SQL 2005. This collection of assemblies is all that you need to control your SQL Server. Be it SQL 2005, or SQL 2000, you can control/manipulate it programmatically using SQL SMO.

Since SQL SMO gave you the ability to treat the database elements such as Table, Database, Stored Procedure, and Trigger as an object, interacting with them should be relatively easy and practical. Additionally, you might want to check out this list of things that make SQL SMO exciting.

OK, lets get our hand dirty with the project. The first step would be importing the references into your project. If you use Visual Studio 2005, look for Microsoft.SqlServer.Smo in the list of .NET assembly when you open ‘Add References’ window.
If you already have MS SQL 2005 installed, you can find the DLLs in the SDKAssemblies folder of your MS SQL 2005 installation folder.
If you don’t have MS SQL 2005 installed in your computer, you might want to download the Express edition HERE.

Next, you could use the following code as reference.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Configuration;
using System.Data.SqlClient;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            //Assuming that you include a Configuration file to your 
            //project and set the key 'DataSource'
            //as the connection string
            SqlConnection conn = new SqlConnection(
                      ConfigurationManager.AppSettings["DataSource"]
            );
           //Instantiate the connection to the server
	   ServerConnection dbConn = new ServerConnection(conn);
	   //Instantiate the Database Server Object
	  Server dbServer = new Server(dbConn);			

          //Traverse the Database Objects
          foreach (Database db in dbServer.Databases)
          {
              if (db.IsAccessible)
              {
                  //traverse the Stored Procedure Objects
                  foreach (StoredProcedure sp in db.StoredProcedures)
                  {
                      foreach (string str in sp.Script())
                          Console.WriteLine(str);
                  }
                  //Traverse the Table Objects
                  foreach (Table tb in db.Tables)
                  {
                      foreach (string str in tb.Script())
                          Console.WriteLine(str);
                  }
                  //Traverse the View Objects
                  foreach (View vw in db.Views)
                  {
                      foreach (string str in vw.Script())
                          Console.WriteLine(str);
                  }
              }
          }
      }
  }
}

Make sure in your app.config you have the DataSource key. You can follow this template for the configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>    
  <appSettings>
    <add key="DataSource" value="data source=db_hostname;initial catalog=db_name;UID=user_id;PWD=password"></add>
  </appSettings>
</configuration>

Have fun!

GD Star Rating
loading...

Incoming Search Term

If you have an old TV set, printers, old cell phones with poor ringtones or other domestic appliances that are no longer useful for you and you're about to dispose them of; don’t do this. Instead you can recycle these goods and recycling is better particularly if the goods are electronics.

2 comments so far

Add Your Comment
  1. Hi Bro,

    If you’re really interested in SQL Server object model and internal behaviour, ping me. I have tons of material that will make you spoiled :).

  2. How can we access a trigger specifying the trigger name or how can show a list of all triggers in all tables of the database using SMO. The SQL Query is as following, but for SMO i need help.

    SELECT OBJ.ID, OBJ.NAME, COM.TEXT,
    (SELECT INR.NAME FROM TEMPDB..SYSOBJECTS AS INR WHERE INR.ID=OBJ.PARENT_OBJ)AS TABLENAME
    FROM TEMPDB..SYSOBJECTS AS OBJ INNER JOIN TEMPDB..SYSCOMMENTS AS COM
    ON OBJ.ID = COM.ID WHERE OBJ.XTYPE= ‘TR’ AND OBJ.CATEGORY = 0 ORDER BY NAME