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!
