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.

Answer 1

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