script all stored procedures into one file

Question

script all stored procedures into one file

GoOnYahGoodThing

SSChasing Mays

Points: 617
More actions
May 11, 2006 at 4:10 am

#172441

Hi,
have been searchinaround but can't find a script that does this...
Is there a script that I can use to get all stored procedure definitions into a single file...
I need to move a bunch of stored procs from one machine to another...
thanks
Vida...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

The easiest way to do it is through the Enterprise Manager click on stored procedures and select the stored procedures you want to script and right click on them and select Generate Script and you can generate the sql script for the all the selected stored procedures.

If you don't want to use EM then this script may help

SELECT

syscomments.text

FROM

sysobjects,syscomments

WHERE

sysobjects.id = syscomments.id

AND

sysobjects.type IN ('P')

AND

sysobjects.category=0

and

sysobjects.name in (<Names of Stored Procedures separated by comma>)

However if the length of the script is more than 4000 this may not work as the text is saved into multiple rows and you may need to write few lines of code to manipulate that.

Prasad Bhogadi
www.inforaise.com

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's another way to get them by using a cursor to enumerate the procs, and simply using sp_helptext:

declare

@isql varchar(2000),

@provname varchar(64)

declare c1 cursor for select name from sysobjects where name not like 'dt_%' and xtype='P'

open c1

fetch next from c1 into @provname

While @@fetch_status <> -1

begin

select @isql = 'sp_helptext @provname '

select @isql = replace(@isql,'@provname',@provname)

print @isql

exec(@isql)

print 'GO'

fetch next from c1 into @provname

end

close c1

deallocate c1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Varadarajan R Newbie Points: 3 More actions · Answer 3

Hi !

I am not sure as to the objective of moving the sps to another machine. If there is a SQL server installed on the target machine, the best way may be to DTS and export to the new server.

If you just want to backup, like our friend has said, you can block mark all the SPs in EM and just Ctrl-C Ctrl-V to Notepad.

Hope this helps.

Varadarajan R

GoOnYahGoodThing SSChasing Mays Points: 617 More actions · Answer 4

Hi guys,

yeah, ehh the reason I want to move the stored procs is we have a live version of the database out there... and we have had a guy working on fixing a few bugs...

We can't DTS over to the live server as its off site.. so I just want to send a big alter script out there which the local dba can run. We get to preserve the production data while fixing the bugs in the stored procs.

ehh Lowell... thanks for your script... quetion tho... if the stored procs are big (some are) then I presume I'd just have to bump of the size of the @isql variable to 6000 or whatever ?

Thanks,

Vida

vadba SSChampion Points: 11133 More actions · Answer 5

Unless the process needs to be fully automated, I would follow Prasad Bhogadi's advice and use Enterprise Manager. Drill down to thew database, right click on the database name, and select the All Tasks | Generate SQL Scripts menu option. Then, click the Show All button to enable all of the choices. Click the All Stored Procedures checkbox. Click the Formatting tab and review the choices. I usually use the default (DROP and CREATE). Then, click the Options tab. The first thing I do is change the script to Windows text (instead of Unicode), but that's optional. If you have the same roles and/or users on the source server, you can check the Script object level permissions checkbox. Otherwise, you'll have to assign permissions on the target server after the stored procedures are created. Hopefully, you assign all permissions to roles and not to individual users. That way, the roles can exist on both the development and the production server, simplifying permission management.

One caveat, this method scripts the SP's in alphabetical order. If you have nested stored procedures (stored proc A calls stored proc B - B is nested), and the you'll get errors when you run the script. You'll have to cut and paste the nested procedure so that it appears in the file BEFORE the calling stored procedure.

Lowell SSC Guru Points: 323518 More actions · Answer 6

you would not need to increase the size of the isql variable; that is just containing the command "sp_helptext your_long_procedure_name" ; i think the max size it could ever be would be like 255 + len(sp_helptext ), so like 255+12= 279 characters;

i just default that cursor at 1000 as i use it for otehr scripts.

the sp_helptext would gives rows and rows of data;

this solution below does two things: it handles the hierarchy/dependancy issue that was mentioned previously, and sticks all the results in a temp table, then foinally pulls it out as a group.

see if this helps at all:

--drop table ##results

--drop table #hierarchy

--because of scope, a #results table would not be available in the dynamic query; use a global temp table instead.

set nocount on

Create Table ##Results(ResultsID int identity(1,1),ResultsText varchar(1000) )

CREATE TABLE #HIERARCHY (TYPE int, ONAME varchar(517), OWNER varchar(517), SEQ int)

INSERT #HIERARCHY

EXEC sp_msdependencies @intrans = 1

--otype: int he above table functions=1, table=8,trigger=256,Procs=16,view=4

declare

@isql varchar(2000),

@procname varchar(64)

declare c1 cursor for select ONAME from #HIERARCHY where type in(1,16) order by seq