x

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

more ▼

asked Oct 12, 2009 at 03:38 AM in Default

Priya gravatar image

Priya
31 1 1 1

(comments are locked)
10|1200 characters needed characters left

8 answers: sort voted first

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.

more ▼

answered Oct 12, 2009 at 05:36 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12, 2009 at 05:46 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

Will SQL Compare do the 'if sp exists drop sp' version? Also the small matter of the £245 :)
Oct 12, 2009 at 06:31 AM Matt Whitfield ♦♦
It certainly will if it generates an upgrade script. Don't know about the full dump, suspect it probably doesn't. I'll try and get one of the team to pop along and answer that!
Oct 12, 2009 at 06:57 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12, 2009 at 09:57 AM

GSquared gravatar image

GSquared
78

Is there a way to make that generate separate files for each proc? Or to do the 'if sp exists drop sp' version?
Oct 12, 2009 at 10:28 AM Matt Whitfield ♦♦
Yes. It has an option for one file for each object. And it can include a Drop command.
Oct 12, 2009 at 02:44 PM GSquared
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 12, 2009 at 06:41 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 12, 2009 at 08:41 AM

Paul Anderson gravatar image

Paul Anderson
1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x67
x3

asked: Oct 12, 2009 at 03:38 AM

Seen: 13072 times

Last Updated: Oct 12, 2009 at 03:50 AM