Scripting with SQL Server Management Objects

When I needed to move about 28500 stored procedures from a SQL Server 2000 to a SQL Server 2005, I turned to the SQL Server Management Studio. It didn't offer any option to move the stored procedures but it did allow to script them to a text file. I launched the "Generate Scripts" task and noticed that after 8 hours of processing only 3500 stored procedures had been scripted. The process had also eaten all the RAM on my workstation. Surely there is a better way.

It would takes ages at this rate so I cancelled the task and turned to the SQL Server Management Objects that are installed together with SQL Server Management Studio. According to MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following code shows the C# application that I wrote to quickly generate scripts for all stored procedures in one database. In order to make it compile, you will need to add references to these assemblies:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.SmoEnum
• Microsoft.SqlServer.ConnectionInfo

• connect to the database
• loops over the collection of all stored procedures
• script it to a text file if it is not a system stored procedure

using System;
using System.IO;
using System.Text;
using System.Collections.Specialized;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SProcScripter
{
  class Program
  {
    static void Main(string[] args)
    {
        int success = 0;
        int failure = 0;

        StreamWriter sw = new StreamWriter("d:\\SProcs.sql");

        // Connect to server and database
        Server srv = new Server("myServerName");
        Database db = srv.Databases["myDatabaseName"];
        Scripter scrp = new Scripter(srv);

        scrp.Options.ScriptDrops = false;
        scrp.Options.WithDependencies = false;

        foreach (StoredProcedure sp in db.StoredProcedures)
        {
            try
            {
                Urn[] smoObjects;
                smoObjects = new Urn[1];
                smoObjects[0] = sp.Urn;

                if (!sp.IsSystemObject)
                {
                    Console.Write("Scripting " + sp.Name);

                    StringCollection sc = scrp.Script(smoObjects);
                    StringBuilder sb = new StringBuilder();

                    foreach (String st in sc)
                    {
                        sb.AppendLine(st);
                        sb.AppendLine("GO");
                    }

                    sw.WriteLine(sb.ToString());

                    success++;
                    Console.WriteLine(" done.");
                }
            }
            catch (Exception ex)
            {
                failure++;
                Console.WriteLine(" failed with exception: " + ex.Message);
            } // try - catch
        } // foreach stored procedure

        sw.Close();

        Console.WriteLine("Success: " + success.ToString());
        Console.WriteLine("Failure: " + failure.ToString());
        Console.WriteLine("Press any key");
        Console.ReadKey();

    } // void Main
  }
}

All stored procedures were generated within 30 minutes! I've tried to make this work faster using a ThreadPool but the SMO components are not thread-safe and all sort of unexpected errors and race conditions popped up. I needed to introduce several locks to keep the object out of each others way. I succeeded in making it work but due to these locks it wasn't really faster than the single threaded version above.