Script SQL Server database, including lookup tables content, by using Microsoft.SqlServer.Management.Smo

If you want to script a SQL Server database and want to include content for the lookup tables, you can use Microsoft.SqlServer.Management.Smo in C#:

  • Add reference to Microsoft.SqlServer.Smo
  • Add reference to Microsoft.SqlServer.ConnectionInfo
  • Add reference to Microsoft.SqlServer.Management.Sdk.Sfc

 

Use the following code to scripts Tables, Content, Store Procedures and Functions

using System;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;
using NUnit.Framework;
namespace Sales.Test.Database
{
[TestFixture]
public class ScriptObjects
{
[Test]
public void ScriptDatabaseTest()
{             
// Reference the SQL Server instance
SqlConnection sqlConnection = new SqlConnection("Data Source=.;Initial Catalog=AdaSales;Integrated Security=SSPI;");
ServerConnection serverConnection = new ServerConnection(sqlConnection);
Server server = new Server(serverConnection);
// Reference the database
Microsoft.SqlServer.Management.Smo.Database database = server.Databases["AdaSales"];
// Delete output folder if it exist
DirectoryInfo outputFolder = new DirectoryInfo(@"C:\Temp");
if (outputFolder.Exists) { outputFolder.Delete(true); }
// Create [Output] folder
outputFolder.Create();
// Create [Tables] output folder
DirectoryInfo tablesOutputFolder = new DirectoryInfo(Path.Combine(outputFolder.FullName, "Tables"));
tablesOutputFolder.Create();
// Loop tables
foreach (Table table in database.Tables)
{
// Generate script
// Include primairykey, foreignkey, constaints, defaults etc.
// Exclude collation information
StringBuilder resultScript = new StringBuilder(string.Empty);
// Only create table when it does not exist
resultScript.AppendFormat("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))", table.Name).Append(Environment.NewLine);
resultScript.AppendLine("BEGIN");
ScriptingOptions options = new ScriptingOptions();
options.DriAll = true;
options.NoCollation = true;
StringCollection scriptLines = table.Script(options);
// Add script to file content
foreach (string scriptLine in scriptLines)
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
resultScript.AppendLine(line.Trim());
}
resultScript.AppendLine("END");
// Write file content to disk
string fileName = string.Format("{0}.sql",table.Name);
File.WriteAllText(Path.Combine(tablesOutputFolder.FullName, fileName), resultScript.ToString());
}
// Create [Content] output folder
DirectoryInfo contentOutputFolder = new DirectoryInfo(Path.Combine(outputFolder.FullName, "Content"));
contentOutputFolder.Create();
// Loop tables for content
foreach (Table table in database.Tables)
{
// Only include lookup tables
if (table.ForeignKeys.Count == 0)
{
// Generate script
// Include content in script
// Exclude table schema (table creation etc.) and Dri (foreignkeys etc.)
StringBuilder resultScript = new StringBuilder(string.Empty);
// Only insert data when table is empty
resultScript.AppendFormat("IF NOT EXISTS (SELECT 1 FROM [dbo].[{0}])", table.Name).Append(Environment.NewLine);
resultScript.AppendLine("BEGIN");
Scripter scripter = new Scripter(server);
ScriptingOptions options = new ScriptingOptions();
options.DriAll = false;
options.ScriptSchema = false;
options.ScriptData = true;
scripter.Options = options;
// Add script to file content
foreach (string scriptLine in scripter.EnumScript(new Urn[] { table.Urn }))
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
resultScript.AppendLine(line.Trim());
}
resultScript.AppendLine("END");
// Write file content to disk
string fileName = string.Format("{0}.sql", table.Name);
File.WriteAllText(Path.Combine(contentOutputFolder.FullName, fileName), resultScript.ToString());
}
}
// Create [Stored Procedures] output folder
DirectoryInfo storedProceduresOutputFolder = new DirectoryInfo(Path.Combine(outputFolder.FullName, "Stored Procedures"));
storedProceduresOutputFolder.Create();
// Loop stored procedures
foreach (StoredProcedure storedProcedure in database.StoredProcedures)
{
// Exclude system stored procedures
if (!storedProcedure.IsSystemObject)
{
// Generate script
// Include drop statements
StringBuilder resultScript = new StringBuilder(string.Empty);
// Append drop statement
resultScript.AppendFormat("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC'))", storedProcedure.Name).Append(Environment.NewLine);
resultScript.AppendLine("BEGIN");
resultScript.AppendFormat("    DROP PROCEDURE [dbo].[{0}]", storedProcedure.Name).Append(Environment.NewLine);
resultScript.AppendLine("END");
resultScript.AppendLine("GO");
// Add script to file content
foreach (string scriptLine in storedProcedure.Script())
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
resultScript.AppendLine(line.Trim());
}
// Write file content to disk
string fileName = string.Format("{0}.sql", storedProcedure.Name);
File.WriteAllText(Path.Combine(storedProceduresOutputFolder.FullName, fileName), resultScript.ToString());
}
}
// Create [Functions] output folder
DirectoryInfo functionsOutputFolder = new DirectoryInfo(Path.Combine(outputFolder.FullName, "Functions"));
functionsOutputFolder.Create();
// Loop stored procedures
foreach (UserDefinedFunction function in database.UserDefinedFunctions)
{
// Exclude system functions
if(!function.IsSystemObject)
{
// Generate script
// Include drop statements
StringBuilder resultScript = new StringBuilder(string.Empty);
// Append drop statement
resultScript.AppendFormat("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))", function.Name).Append(Environment.NewLine);
resultScript.AppendLine("BEGIN");
resultScript.AppendFormat("    DROP FUNCTION [dbo].[{0}]", function.Name).Append(Environment.NewLine);
resultScript.AppendLine("END");
resultScript.AppendLine("GO");
// Add script to file content
foreach (string scriptLine in function.Script())
{
string line = scriptLine;
line = line.Replace("SET ANSI_NULLS ON", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER ON", string.Empty);
line = line.Replace("SET ANSI_NULLS OFF", string.Empty);
line = line.Replace("SET QUOTED_IDENTIFIER OFF", string.Empty);
resultScript.AppendLine(line.Trim());
}
// Write file content to disk
string fileName = string.Format("{0}.sql", function.Name);
File.WriteAllText(Path.Combine(functionsOutputFolder.FullName, fileName), resultScript.ToString());
}
}
}
}
}

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

4 comments

  1. […] to disk by using a C# console application and a few Microsoft.SqlServer DLL’s (credits to Roel van Lisdonk who blogged about […]

  2. […] to disk by using a C# console application and a few Microsoft.SqlServer DLL’s (credits to Roel van Lisdonk who blogged about […]

  3. Chris Smith says:

    This is awsome! I am trying to add to this script by adding the indexes after creating all the tables, getting a bit stuck though!

  4. Harinatha Reddy says:

    Thakyou so much your code helps me alot