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

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

 

 

4 Comments

  1. Pingback: Tecsupra – Script SQL Server table content (data)

  2. Pingback: tecsupra.com - Leonardo D'Ippolito – Script SQL Server table content (data)

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.