Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there a non-interactive (read: script-able) way to dump all stored procedures to disk?

We keep versions of our stored procedures in the repository to track changes and for deployment and rollback purposes. Currently whenever we want to modify a stored procedure you have to pull it out of the DB directly when you begin your change.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

What are you using for your repository? I don't understand why you have to pull it out of the db directly when you begin your change. If your release management is such that you have them in your repository you should just be able to pull the file out of your repository, fire up a connection in Management Studio and run the ALTER PROCEDURE.

That said, you can extract the code of an object using the INFORMATION_SCHEMA.ROUTINES system view or the syscomments view.

Using INFORMATION_SCHEMA.ROUTINES it's something like this:

SELECT Routine_Definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE Routine_Type='Procedure'

Using syscomments it's something like this:

SELECT OBJECT_NAME(id),text
FROM syscomments 
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
share|improve this answer
    
Preaching to the choir. My personal development workflow is very efficient and organized. The problem arises when the DBA makes a change on-the-fly to a stored procedure. Despite my repeated instructions on using revision control, he just doesn't do it. And then there's the documents guy who doesn't even understand revision control. Even my manager feels it necessary to go in and modify them on-the-fly at times. Bottom line, they can't be trusted to use the repo and I can't trust the repository to have up-to-date versions. Thus, nightly cron to dump them all and commit to master branch. –  Jake Wharton May 28 '10 at 4:18
    
@Jake: Ahhhh, that sucks. I feel for you man. Best of luck on that! I'll hand you my 2x4 so you can run down the line with it knocking heads. –  squillman May 28 '10 at 11:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.