How can I automate the "generate scripts" task in SQL Server Management Studio 2008?

Question

I'd like to automate the script generation in SQL Server Management Studio 2008.

Right now what I do is :

• Right click on my database, Tasks, "Generate Scripts..."
• manually select all the export options I need, and hit select all on the "select object" tab
• Select the export folder
• Eventually hit the "Finish" button

Is there a way to automate this task?

Edit : I want to generate creation scripts, not change scripts.

Solution 1

What Brann is mentioning from the Visual Studio 2008 SP1 Team Suite is version 1.4 of the Database Publishing Wizard. It's installed with sql server 2008 (maybe only professional?) to \Program Files\Microsoft SQL Server\90\Tools\Publishing\1.4. The VS call from server explorer is simply calling this. You can achieve the same functionality via the command line like:

sqlpubwiz help script

I don't know if v1.4 has the same troubles that v1.1 did (users are converted to roles, constraints are not created in the right order), but it is not a solution for me because it doesn't script objects to different files like the Tasks->Generate Scripts option in SSMS does. I'm currently using a modified version of Scriptio (uses the MS SMO API) to act as an improved replacement for the database publishing wizard (sqlpubwiz.exe). It's not currently scriptable from the command line, I might add that contribution in the future.

Scriptio was originally posted on Bill Graziano's blog, but has subsequently been released to CodePlex by Bill and updated by others. Read the discussion to see how to compile for use with SQL Server 2008.

http://scriptio.codeplex.com/

EDIT: I've since started using RedGate's SQL Compare product to do this. It's a very nice replacement for all that sql publishing wizard should have been. You choose a database, backup, or snapshot as the source, and a folder as the output location and it dumps everything nicely into a folder structure. It happens to be the same format that their other product, SQL Source Control, uses.

Solution 2

SqlPubwiz has very limited options compared to the script generation in SSMS. By contrast the options available with SMO almost exactly match those in SSMS, suggesting it is probably even the same code. (I would hope MS didn't write it twice!) There are several examples on MSDN like this one that show scripting tables as individual objects. However if you want everything to script correctly with a 'full' schema that includes 'DRI' (Declarative Referential Integrity) objects like foreign keys then scripting tables individually doesn't work the dependencies out correctly. I found it is neccessary to collect all the URNs and hand them to the scripter as an array. This code, modified from the example, works for me (though I daresay you could tidy it up and comment it a bit more):

    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Sdk.Sfc;
    // etc...

    // Connect to the local, default instance of SQL Server. 
    Server srv = new Server();

    // Reference the database.  
    Database db = srv.Databases["YOURDBHERE"];

    Scripter scrp = new Scripter(srv);
    scrp.Options.ScriptDrops = false;
    scrp.Options.WithDependencies = true;
    scrp.Options.Indexes = true;   // To include indexes
    scrp.Options.DriAllConstraints = true;   // to include referential constraints in the script
    scrp.Options.Triggers = true;
    scrp.Options.FullTextIndexes = true;
    scrp.Options.NoCollation = false;
    scrp.Options.Bindings = true;
    scrp.Options.IncludeIfNotExists = false;
    scrp.Options.ScriptBatchTerminator = true;
    scrp.Options.ExtendedProperties = true;

    scrp.PrefetchObjects = true; // some sources suggest this may speed things up

    var urns = new List<Urn>();

    // Iterate through the tables in database and script each one   
    foreach (Table tb in db.Tables)
    {
        // check if the table is not a system table
        if (tb.IsSystemObject == false)
        {
            urns.Add(tb.Urn);
        }
    }

    // Iterate through the views in database and script each one. Display the script.   
    foreach (View view in db.Views)
    {
        // check if the view is not a system object
        if (view.IsSystemObject == false)
        {
            urns.Add(view.Urn);
        }
    }

    // Iterate through the stored procedures in database and script each one. Display the script.   
    foreach (StoredProcedure sp in db.StoredProcedures)
    {
        // check if the procedure is not a system object
        if (sp.IsSystemObject == false)
        {
            urns.Add(sp.Urn);
        }
    }

    StringBuilder builder = new StringBuilder();
    System.Collections.Specialized.StringCollection sc = scrp.Script(urns.ToArray());
    foreach (string st in sc)
    {
        // It seems each string is a sensible batch, and putting GO after it makes it work in tools like SSMS.
        // Wrapping each string in an 'exec' statement would work better if using SqlCommand to run the script.
        builder.AppendLine(st);
        builder.AppendLine("GO");
    }

    return builder.ToString();

Solution 3

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

Solution 4

You can use SQL Server Management Object (SMO) to automate SQL Server 2005 management tasks including generating scripts: http://msdn.microsoft.com/en-us/library/ms162169.aspx.

Solution 5

If you're a developer, definitely go with SMO. Here's a link to the Scripter class, which is your starting point:

Scripter Class

View more solutions

Comments

[Alexa Adrian]

did you found the answer? I want to do this too, I used Publishing, it saved on hard, don't know where and there I don't have all options that are when generating a script :-?

[Jay Jay Jay]

As mentioned in several of the answers,Use SMO if you are a developer

[Brann]

That's not exactly what I need. I'd like to get the creation scripts (my final goal is to automatically checkin those files into my source control system)

[Brann]

Unfortunately, it doesn't support SqlServer 2008

[Serge Wautier]

How does it help automating the task?

[OlduwanSteve]

I spent a while trying out this solution, but in the end I turned to C# and using the SMO. The problem I couldn't easily find a way past is that the example code scripts each table individually. Once you start adding in 'DRI' (Declarative Referential Integrity) objects like foreign keys it won't get the dependencies right and/or it will be very slow.

[cubetwo1729]

You can look at the class Microsoft.SqlServer.Management.SqlScriptPublish.ScriptPublis‌​hWizard from the assembly C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.SqlServer.M‌​anagement.SqlScriptP‌​ublishUI.dll. This is what SSMS uses. (Alternatively, you could look at the class Microsoft.SqlServer.Management.UI.GenerateScript from the assembly C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ReplicationDialog.dll‌​.)

[PeterX]

Is there an equivalent in 2012? I'd just be happy if the wizard remembered my settings like "Script Indexes".

[Jeremy]

Version 1.2 says it supports 2000 and 2005, but I just used it to script a database on 2008 with 3800+ tables and it worked fine. It didn't include compress options, which weren't introduced until 2008. I've also just tested against a 2008 R2 database and it scripts that fine as well.

[anjani]

I have just tried SchemaZen and I'm really impressed. Worked first time. Thanks Seth!

[zanlok]

The example code and links here were a great start, and should be the most complete answer to the OP (which was my exact question as well).

[zanlok]

@PeterX SMSS > Tools > Options > SQL Server Object Explorer > Scripting

[Simon Hutchison]

For 2012 follow this guide: simple-talk.com/sql/database-administration/…

[John]

@Seth so what are you using to script the objects? I did not see a single reference to any Microsoft.SqlServer anything (well there was a single unused using statement).

[jk7]

@zanlok very useful comment. Unfortunately they are missing the "Include unsupported statements" option in SQL Server 2014.

[Seth Reno]

@John - Schema Zen contains it's own library for scripting. It reads the schema into a model then generates the scripts based on the model. See github.com/sethreno/schemazen/blob/master/model/Models/… for a simple example.

[mkurz]

I hacked together a C# app which allows you to generate SQL server scripts from the command line in Linux. All you need is .Net Core 2 preview: github.com/mkurz/SQLServerScripter

[Fred Smith]

I have forked the scriptio codeplex repository to GitHub github.com/fredatgithub/Scriptio