SQL Server: Programatically trigger export of db schema

Question

SQL Server 2014, Std edition

We have seen and used, over the years, a variety of sprocs and non-MS tools to export the DB schema.

The way we really like to do this is the built in utility, in SSMS, as described here on SO: https://stackoverflow.com/a/12036513/147637

We strongly prefer the MS tool, because it is more complete than hand-rolled tools, and provides super clean, properly organized and structured output.

However, we want to automate this.

How can the MS engine be driven programatically?

Answer 1

These days, by far the best way is both open source and from Microsoft (what a world we live in):

mssql-scripter

The README says it all, so I'll just quote it:

mssql-scripter is the multiplatform command line equivalent of the widely used Generate Scripts Wizard experience in SSMS. You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server running anywhere, Azure SQL Database, and Azure SQL Data Warehouse. You can save the generated T-SQL script to a .sql file or pipe it to standard nix utilities (for example, sed, awk, grep) for further transformations. You can edit the generated script or check it into source control and subsequently execute the script in your existing SQL database deployment processes and DevOps pipelines with standard multiplatform SQL command line tools such as sqlcmd.

Answer 2

For automation you would likely be best served by using SQL Server Management Objects (SMO). It will require doing a little bit of programming, but it is designed for easy navigation of the database / SQL structure. Many people use it for generating scripts just like this.

SSMS isn't designed for automation. And even if you could get it to do parts of this in a somewhat automated fashion, you are probably not guaranteed to have those configuration settings remain consistent across computers or even users on the same computer. But by using SMO (mentioned above) you are in full control and can easily automate it and can configure it by hard-coding settings or making it more flexible by putting some / all settings in an app.config file.

Some examples of programming SMO are:

• Automated Script-generation with Powershell and SMO (mentioned by @Kin in a comment on the Question)
• SMO Tutorial 1 of n - Programming data storage objects
• Getting started with SQL Server Management Objects (SMO)
• Scripting Database Objects using SMO (Updated)

Answer 3

This can be done via SMO objects. Below is an example of this using C#. This uses tables, but can be done for views, stored procedures, etc. by changing the Table object to View, StoredProcedure, or whatever other object you want to script. You can use the Name property to filter by name as in the comment below. Another option is to String.StartsWith method of Name to only get objects with names that start with a certain text, i.e. Name.StartsWith("Prefix"). DDL is written to .sql files that can be executed or used for other purposes. If you want to automate this create an SSIS package that contains a Script Task with this code. You can then schedule the package to run as a job using SQL Agent. References to the Microsoft.SqlServer.Smo, Microsoft.SqlServer.Management.Sdk.Sfc, and Microsoft.SqlServer.ConnectionInfo dlls will be necessary as well.

        StringBuilder sb = new StringBuilder();
        Server serv = new Server(@"YourSQLSeverInstance");

        //Windows authentication
        serv.ConnectionContext.LoginSecure = true;

        Database db = serv.Databases["YourDatabase"];
        ScriptingOptions scriptOpt = new ScriptingOptions();

        //this can be updated to views, stored procedures, or other objects.
        foreach (Table T in db.Tables)
        {
            //omit system objects
            //can use T.Name to filter by name
            if (!T.IsSystemObject)
            {
                scriptOpt.FileName = @"C:\Test\" + T.Name + ".sql";
                StringCollection sc = T.Script(scriptOpt);
                foreach (string s in sc)
                    Console.WriteLine(s);
            }
        }