question

Priya avatar image
Priya asked

Generate script file for each stored procedure

I am using "Microsoft SQL Server Management Studio Express 9.00.2047.00" as the interface for SQL Server 2005 Developer Edition.

I wanted to create a separate SQL file for each stored procedure in my database. The generated script should have the format like this.

If sp exists drop sp
Create sp

I am very new to this area. Please help me
Thanks in advance
Priya

scriptgenerate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Melvyn Harbour 1 avatar image
Melvyn Harbour 1 answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image
Phil Factor answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Paul Anderson avatar image
Paul Anderson answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GSquared avatar image
GSquared answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cade Roux avatar image
Cade Roux answered

I have used and like APEX SQL Script, it can be driven from the command-line (it has options besides one object per script), and you can make templates for the names.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered

I use ScriptDB http://scriptdb.codeplex.com/ to generate a backup of our source. Since it is open source, you can modifiy it to your hearts delight.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rinosh avatar image
Rinosh answered

Thanks Mr Matt Whitfield♦ Your code was very useful for me

Rinosh.K.Sasidharan rinosh.wordpress.com rinosh.co.cc

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.