Knowledgebase

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());
                }
            }
        }
    }
}