Generate script file for each stored procedure

It's very easy to do this with SMO, if you can use .NET:

using (SqlConnection sqlConn = new SqlConnection("Server=MyServer;Integrated Security=True;")) { sqlConn.Open(); ServerConnection srvConn = new ServerConnection(sqlConn); Server srv = new Server(srvConn); Database db = srv.Databases["MyDatabase"];

         foreach (StoredProcedure sp in db.StoredProcedures)
         {
             using (TextWriter tw = new StreamWriter("c:\\procs\\" + sp.Name + ".sql"))
             {
                 foreach (string sqlScript in sp.Script())
                 {
                     tw.WriteLine(sqlScript);
                     tw.WriteLine("GO");
                 }
             }
         }
     }

This example connects to a server called MyServer, and saves the script for all stored procedures in the database called MyDatabase into the folder c:\procs.

It will need two modifications, potentially, which I will leave up to you:

1) You will need to ensure that the file names used are valid, if you have any stored procedures with invalid file name characters in them

2) You will need to modify the code to emit the 'if sp exists drop sp' part. SMO can do the 'if sp does not exist create sp' version, but not the 'if sp exists drop sp' AFAIK.

SQL Compare will do it for you quite nicely. It's what we actually use to version control the database schemata that are used on SSC. We dump the whole database schema to a folder hierarchy and then put that in source control, along with the C# code.

Any particular reason to not just use the Generate Scripts feature in Management Studio? Right-click the database, Tasks, Generate Scripts, pick the options you want, done.

You can do this a number of ways. Basically, the process is built in to SMO/DMO, which does it very well, and allows you several alternatives, depending on your method of source control.

Nowadays, the best way of doing it involves Powershell or another scripting language. I find it most convenient to use a stored procedure to do it, as I can execute it from a query window or put it on the SQL Server Agent, and twiddle the knobs to get the exact scripting I want without leaving the query window. This involves using the rather antiquated DMO. Deprecation of DMO was announced in SQL Server 2005. In SQL Server 2008,is shipped but not automatically installed. There are two places to get DMO with SQL Server 2008, if you have the intallation DVD, you'll find it here ..\x86\Setup\x86\SQLServer2005_BC.msi, or you can download it from here SQL Server 2008 Feature Pack @ the Microsoft Download Center but it is best to google the "SQL Server Feature Pack" for the latest version.

The script is a bit long to paste into this answer but it is described in this article here Automating Common SQL Server Tasks using DMO and the code is here

Easiest way I know of that doesn't quite meet your layout criteria but acheives the same purpose is to simply highlight all the procedures in the Object Explorer Details in SSMS, and then right click and choose Script as Drop and Create...

Please ignore this as I didn't read the full spec... Ho-hum. (hangs head in developer shame).