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.
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.
