Using SMO to generate the script of triggers in SQL server 2005

The below is the code written in C# to get the scripts of the triggers, stored procedures, views and user defined functions available in the SQL Server database.

This is done using the SMO class exposed by the microsoft. To use SMO in our C# code we need to reference to the Microsoft's SMO dll. This dll can be found in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies directory. You'll need to add references to dll as shown below

using Microsoft.SqlServer.Management.Smo;

This is the code which we can add as a method to any of the existing class file

Server oServer = new Server();

try

{

oServer.ConnectionContext.ServerInstance = "TestInstance";

oServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");

oServer.ConnectionContext.LoginSecure = false;

oServer.ConnectionContext.Login = "sa";

oServer.ConnectionContext.Password = "password";

oServer.ConnectionContext.Connect();

TextWriter oTextWriter = null;

StringBuilder oSBText = null;

foreach (Database oDB in oServer.Databases)

{

if (oDB.Name.ToUpper().Equals("DB1"))

{

oTextWriter = new StreamWriter(@"D:\DBScripts\DbScripts1.sql");

}

else if (oDB.Name.ToUpper().Equals("DB2"))

{

oTextWriter = new StreamWriter(@"D:\DBScripts\DbScriptss.sql");

oSBText = new StringBuilder();

}

if (oTextWriter != null)

{

foreach (Table oTable in oDB.Tables)

{

TriggerCollection oTriggerCollect = oTable.Triggers;

if (oTriggerCollect.Count > 0)

{

oSBText.Append("\n" + "--" + oDB.Name + "\n" + "--" + oTable.Name);

foreach (Trigger oTrg in oTriggerCollect)

{

try

{

oSBText.Append("\n" + oTrg.TextHeader + "\n" + oTrg.TextBody);

}

catch (Exception Ex)

{

TextWriter oErrorWriter = new StreamWriter(@"D:\DBScripts\Error\Error.txt");

oErrorWriter.Write(Ex.Message);

oErrorWriter.Close();

}

}

}

}

foreach (StoredProcedure oSP in oDB.StoredProcedures)

{

try

{

if (oSP.IsSystemObject == false)

{

oSBText.Append("\n" + "--" + oDB.Name + "\n" + "--" + oSP.Name + "\n");

StringCollection sc = oSP.Script();

foreach (string s in sc)

oSBText.Append("\n" + s);

}

}

catch (Exception Ex)

{

TextWriter oErrorWriter = new StreamWriter(@"D:\DBScripts\Error\Error.txt");

oErrorWriter.Write(Ex.Message);

oErrorWriter.Close();

}

}

foreach (View oV in oDB.Views)

{

try

{

oSBText.Append("\n" + "--" + oDB.Name + "\n" + "--" + oV.Name + "\n");

StringCollection sc = oV.Script();

foreach (string s in sc)

oSBText.Append("\n" + s);

}

catch (Exception Ex)

{

TextWriter oErrorWriter = new StreamWriter(@"D:\DBScripts\Error\Error.txt");

oErrorWriter.Write(Ex.Message);

oErrorWriter.Close();

}

}

foreach (UserDefinedFunction oUDF in oDB.UserDefinedFunctions)

{

try

{

oSBText.Append("\n" + "--" + oDB.Name + "\n" + "--" + oUDF.Name + "\n");

StringCollection sc = oUDF.Script();

foreach (string s in sc)

oSBText.Append("\n" + s);

}

catch (Exception Ex)

{

TextWriter oErrorWriter = new StreamWriter(@"D:\DBScripts\Error\Error.txt");

oErrorWriter.Write(Ex.Message);

oErrorWriter.Close();

}

}

oTextWriter.Write(oSBText.ToString());

oTextWriter.Close();

oTextWriter = null;

oSBText = null;

}

}

Console.WriteLine("Successfully Written to the file!!!");

}

catch (Exception Ex)

{

TextWriter oErrorWriter = new StreamWriter(@"D:\DBScripts\Error\Error.txt");

oErrorWriter.Write(Ex.Message);

oErrorWriter.Close();

}

finally

{

if (oServer.ConnectionContext.IsOpen)

{

oServer.ConnectionContext.Disconnect();

}

}

To Learn more about SMO follow the below given links.

http://msdn2.microsoft.com/hi-in/magazine/cc163409(en-us).aspx

http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated