Generate SQL script using beloved C#

Generating SQL script is a pretty classy way to obtain queries for schema or data or schema and data which helps to get some sort of backup of your database or get build up queries to regenerate the database entirely. But what if we need to generate SQL scripts programmatically using hearty C#? The SqlManagementObjects (SMO) nuget package is here to help.

The SqlManagementObjects (SMO) nuget package is used to programmatically handle SQL Server and Azure database. Using SMO we will generate queries for schema and data of tables, views, and stored procedures. We have to do some initial configuration to get started; will mention the file where we need to obtain the script and configure the SMO script options. In this article we will make a small Console Application to generate a script of our database named foo_db.

Install Nuget Package

Create a C# console application and install the SqlManagementObjects package from Nuget.

Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 161.44091.28

Initialize file path and connection string

var fileName         = @"C:\csharp-sql-script-generator\backup.sql";
var connectionString = @"Data Source=DESKTOP-6AQPB1S\SQLEXPRESS; Database=foo_db; Integrated Security=true;";
var databaseName     = "foo_db";
var schemaName       = "dbo";

Simply check existence of the file:

if (File.Exists(fileName))
 File.Delete(fileName);

Setup SMO server and scripting options

var server    = new Smo.Server(new ServerConnection(new SqlConnection(connectionString)));
var options   = new Smo.ScriptingOptions();
var databases = server.Databases[databaseName];
options.FileName                = fileName;
options.EnforceScriptingOptions = true;
options.WithDependencies        = true;
options.IncludeHeaders          = true;
options.ScriptDrops             = false;
options.AppendToFile            = true;
options.ScriptSchema            = true;
options.ScriptData              = true;
options.Indexes                 = true;

Generate scripts

Fetch tables, views and procedures to iterate on based on the provided schema name. Once obtained the entities, iterate them to generate SQL scripts using EnumScript and Script methods. Set the option ScriptData and WithDependencies to false when we don’t need the data.

Fetch tables, views and procedures:

var tables     = databases.Tables.Cast().Where(i => i.Schema == schemaName);
var views      = databases.Views.Cast().Where(i => i.Schema == schemaName);
var procedures = databases.StoredProcedures.Cast().Where(i => i.Schema == schemaName);

Iterate the entities:

foreach (Smo.Table table in tables)
{
 databases.Tables[table.Name, schemaName].EnumScript(options);
}
options.ScriptData       = false;
options.WithDependencies = false;
foreach (Smo.View view in views)
{
 databases.Views[view.Name, schemaName].Script(options);
}
foreach (Smo.StoredProcedure procedure in procedures)
{
 databases.StoredProcedures[procedure.Name, schemaName].Script(options);
}

That’s it! Quite straight forward code to get our task done, thanks to SqlManagementObjects (SMO). The complete code is given below:

using System;
using System.Linq;
using System.IO;
using Microsoft.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Smo = Microsoft.SqlServer.Management.Smo;
namespace SQLScriptGeneratorApp
{
    class Program
    {
        static void Main()
        {
            var fileName         = @"C:\csharp-sql-script-generator\backup.sql";
            var connectionString = @"Data Source=DESKTOP-6AQPB1S\SQLEXPRESS; Database=foo_db; Integrated Security=true;";
            var databaseName     = "foo_db";
            var schemaName       = "dbo";
if (File.Exists(fileName))
                File.Delete(fileName);
try
            {
                var server    = new Smo.Server(new ServerConnection(new SqlConnection(connectionString)));
                var options   = new Smo.ScriptingOptions();
                var databases = server.Databases[databaseName];
options.FileName                = fileName;
                options.EnforceScriptingOptions = true;
                options.WithDependencies        = true;
                options.IncludeHeaders          = true;
                options.ScriptDrops             = false;
                options.AppendToFile            = true;
                options.ScriptSchema            = true;
                options.ScriptData              = true;
                options.Indexes                 = true;
var tables     = databases.Tables.Cast().Where(i => i.Schema == schemaName);
                var views      = databases.Views.Cast().Where(i => i.Schema == schemaName);
                var procedures = databases.StoredProcedures.Cast().Where(i => i.Schema == schemaName);
Console.WriteLine("SQL Script Generator");
Console.WriteLine("\nTable Scripts:");
                foreach (Smo.Table table in tables)
                {
                    databases.Tables[table.Name, schemaName].EnumScript(options);
                    Console.WriteLine(table.Name);
                }
options.ScriptData       = false;
                options.WithDependencies = false;
Console.WriteLine("\nView Scripts:");
                foreach (Smo.View view in views)
                {
                    databases.Views[view.Name, schemaName].Script(options);
                    Console.WriteLine(view.Name);
                }
Console.WriteLine("\nStored Procedure Scripts:");
                foreach (Smo.StoredProcedure procedure in procedures)
                {
                    databases.StoredProcedures[procedure.Name, schemaName].Script(options);
                    Console.WriteLine(procedure.Name);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Exception occured: {ex.Message}");
            }
        }
    }
}

Download Code

Git Repository

Hope this article helps.

Originally published at https://codeomelet.com.