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:

Have fun!

Share and Enjoy

 

Possibly Related Posts

• Storm is coming
• MS-SQL Stored Procedure for Absolute Beginner, Part 1
• Reading CSV File with .NET

 

SQLSmo (4), smo database connection string (2), c# sqlsmo (1), traversing string in sql (1), traversing all objects in sql server (1), traverse in string sql server (1), sqlsmo vb net excel (1), sqlsmo data query (1), SQL-SMO coding in vb net (1), sql traverse string (1), sql smo download (1), sql server traverse a string (1), procedure to traverse in database list (1), how to list of all tables of sql server database using smo using vb net (1), get list of all databases asp net smo (1), find all table in database with sql smo (1), configure and sqlsmo and VB Net (1), using sqlsmo (1)

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.