Script Search

Question

Post reply

Script Search

john-902052

SSCommitted

Points: 1578
More actions
April 30, 2009 at 8:25 am

#130518

I was wondering if anyone has a script that can take a DB name as input and dump out all of the stored procedures to disk files for backup purpose.
I would like to be able to do this and then I can post the SPs to our subversion. If no one has this I will write it and share it here but thought I would check first.
Thanks in advance
JD

Viewing 9 posts - 1 through 8 (of 8 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You may try to start from the stored procedure, sp_msforeachdb

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 2

john (4/30/2009)
I was wondering if anyone has a script that can take a DB name as input and dump out all of the stored procedures to disk files for backup purpose.
I would like to be able to do this and then I can post the SPs to our subversion. If no one has this I will write it and share it here but thought I would check first.
Thanks in advance
JD

u can use ssis/sqlcmd/sqldmo to get the list of all database objects...

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

You can use bcp with query on information_schema.routines. The column routine_definition contains the routine’s script. The column routine_type can help you filter only the records that are stored procedure. One last thing – I didn’t try it my self, but I don’t see any reason that it shouldn’t work.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

You can indeed write something rather easily to accomplish this, but if you're looking for a well priced tool that is very well built and will handle this and much much more ... you may want to look into SQLClue: http://bwunder.com/SQLClue.aspx

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Adi Cohn (4/30/2009)
You can use bcp with query on information_schema.routines. The column routine_definition contains the routine’s script. The column routine_type can help you filter only the records that are stored procedure. One last thing – I didn’t try it my self, but I don’t see any reason that it shouldn’t work.

This is a nice idea but it won't work. The ROUTINE_DEFINITION column is NVARCHAR(4000) so it only contains the first 4000 characters of the script.

The "definition" column of the "sys.sql_modules" system view does have the complete definition, however, you still have issues of how line-breaks will be handled by the bcp formatter, and whether the textwidth and maximum text output settings affect bcp.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 6

The easiest way to do this would be get a product that does it for you. Red Gate's SQL Doc will probably do what you want, and then a bunch more that's really cool.

Edit:

Thought about it for a minute after I posted this, and a couple of questions come to mind:

First, why only back up procs? They're going to be pretty much useless if you don't also have table definitions at the very least.

Second, wouldn't some form of source control be a better idea than just backing up proc scripts?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

StarNamer SSCrazy Eights Points: 8744 More actions · Answer 7

I use ScriptDb from codeplex.com.

It does the whole database (tables, views, functions, etc) in one go so you can then just compare with your reference copy.

Derek

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

Here is a VbScript I have used a number of times with great success:

How to Get the Scripts for SQL Server Objects - SQLServerCentral