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.