Script out only SP's in a db - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Script out only SP's in a db

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-17 : 13:34:17

Hi,

I need to script out all the stored procedures in a database into seperate .txt files. There are around 200 SP's and is time consuming to script out individually.

Is there any method that could be used to script out all the SP's to seperate files ?.

Thanks,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-17 : 13:39:09

Right click on Databse node and click All Tasks, Generate Script. In the Generate script dialog box, select All Stored Procedures check box.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-17 : 14:07:26

I tried generating the script. But The script mode allows me to script it to only one file. All the SP's gets generated to one file.
I checked out the scripting options tab, but i did not see anything to script out in individual files..
Is this option not available..

hey001us
Posting Yak Master

185 Posts

Posted - 2008-01-17 : 14:08:07

I believe there is an option
Option tab>> File to Generate
Create one file or create one file per object

hey

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-17 : 14:12:46

I checked out and the Create 1 file per object option is present in SQL 2000.
But i am using SQL 2005 and i am not sure what is the equivalent option in Sql2005 ?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-17 : 14:35:11

there is a radio button with the options
- single file
- file per object

tick "file per object"

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-17 : 15:06:05

The steps that go through are:
Select database
Choose Script options - I leave all the default options
In Choose object types - I choose the Stored Procedures
In the next screen i select the SP's
In the output Option screen There are 3 radio buttons that i see.
(1) Script to file (Save as Unicode text or Ansi text)
(2) Script to clipboard
(3) Script to new query window
These are the only options that i see.

Am i missing something..

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-17 : 16:17:02

Looks like we cannot script out 1 script file per object in sql server 2005.

http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-17 : 16:39:11

check this out for scripting 1 object per file in sql server 2005

http://weblogs.sqlteam.com/billg/archive/2005/11/22/8414.aspx

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-17 : 16:39:28

yes you can!

Right-click Database in SSMS
Choose Tasks
Choose Generate Scripts
Select database, uncheck "script all objects in the selected database", click next
click next thru the options screen
click stored procedures, click next
click select all, click next
Tick "Script to File"
Tick "File Per Object"
Type in path
Click next
Click Finish

This works. Are you using SQL Server Management Studio, or some other tool?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-17 : 16:50:54

here's an open source cmd line tool I wrote that will do it. useful if you hate GUIs as I do:

http://www.codeplex.com/scriptdb

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 02:27:13

quote:
Originally posted by pvccaz

Hi,

I need to script out all the stored procedures in a database into seperate .txt files. There are around 200 SP's and is time consuming to script out individually.

Is there any method that could be used to script out all the SP's to seperate files ?.

Thanks,

Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2008-01-18 : 04:20:37

Russel,
Is that possible in SQL Server Express Edition.

RKNAIR

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-18 : 09:41:15

quote:
Originally posted by ratheeshknair

Russel,
Is that possible in SQL Server Express Edition.

RKNAIR

ha ha i knew that question was coming. i dont know. dont have express. i use EE and SE

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2008-01-18 : 11:57:36

Okay.. I installed Service Pack 2 for SQL Server 2005 and now i see the option to script out 1 file per object and scripted out SP's in individual files. This option was not present earlier before i applied SP2.

Thanks for all your responses !!.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources