Automate Your Database Schema Scripting with C# and SMO

mssql-schema-dump.md

Exporting SQL Server database structure into SQL script files

Scripting your SQL Server database schema is one such task that can benefit significantly from automation. Using C# and SQL Server Management Objects (SMO), you can easily generate scripts for your database schema and data.

Automating database scripting ensures that your schema and data scripts are always up-to-date and reduces the risk of human error. It is particularly useful for version control, backups, and migration tasks.

By integrating it into your workflow, you can significantly enhance your database management processes.

Prerequisites

Before you begin, ensure you have the following:

• Visual Studio or VSCode with C#
• .NET SDK installed
• Access to a SQL Server instance

Install packages:

Add the SMO packages required for scripting:

dotnet add package Microsoft.SqlServer.ConnectionInfo
dotnet add package Microsoft.SqlServer.SqlManagementObjects
dotnet add package Microsoft.TeamFoundationServer.Client

Example:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Collections.Specialized;

namespace Schema;

class Program
{
    static void Main(string[] args)
    {
        // Replace with your server and database details
        string serverName = "localhost";
        string databaseName = "DEMO";
        string outputFile = @"schema.sql";
        string userName = "sa";
        string password = "***";

        // Create a server connection
        ServerConnection serverConnection = new ServerConnection(serverName, userName, password);
        Server server = new Server(serverConnection);
        Database database = server.Databases[databaseName];

        // Set scripting options
        // https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.scriptingoptions?view=sql-smo-160&redirectedfrom=MSDN#properties_

        Scripter scripter = new Scripter(server)
        {
            Options =
                {
                    AnsiPadding = false,
                    AppendToFile = false,
                    IncludeIfNotExists = false,
                    ContinueScriptingOnError = false,
                    ConvertUserDefinedDataTypesToBaseType = false,
                    WithDependencies = true,
                    IncludeHeaders = false,
                    IncludeScriptingParametersHeader = false,
                    DriIncludeSystemNames = false,
                    SchemaQualify = true,
                    Bindings = false,
                    NoCollation = true,
                    Default = true,
                    ScriptForCreateDrop = true,
                    ExtendedProperties = true,
                    // SQL Server 2022
                    TargetServerVersion = SqlServerVersion.Version160,
                    TargetDatabaseEngineType = DatabaseEngineType.Standalone,
                    TargetDatabaseEngineEdition = DatabaseEngineEdition.Enterprise,
                    LoginSid = false,
                    Permissions = false,
                    ScriptOwner = false,
                    Statistics = false,
                    ChangeTracking = false,
                    DriAllConstraints = true,
                    ScriptDataCompression = true,
                    DriForeignKeys = true,
                    FullTextIndexes = false,
                    DriIndexes = true,
                    DriPrimaryKey = true,
                    Triggers = false,
                    DriUniqueKeys = true,
                    //FileName = outputFile
                }
        };

        using (StreamWriter writer = new (outputFile))
        {
            // Script all objects in the database
            foreach (Table table in database.Tables)
            {
                if (table.IsSystemObject)
                {
                    continue;
                }

                Console.WriteLine($"{table.Name} - {table.Urn}");

                StringCollection stringCollection = scripter.Script(new Urn[] { table.Urn });

                foreach (string line in stringCollection)
                {
                    Console.WriteLine(line);
                    writer.WriteLine(line + "\nGO\n");
                }
            }

        }

        Console.WriteLine($"Script generation complete. Output file: {outputFile}");
    }
}