Script Search

  • 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

  • You may try to start from the stored procedure, sp_msforeachdb

  • 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]

  • 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/

  • 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

  • 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]

  • 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

  • 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

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

    How to Get the Scripts for SQL Server Objects - SQLServerCentral

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

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

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