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!
About Hardono
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 :).
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