0

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.

Improve this question

1 Answer 1

Reset to default
1

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
Improve this answer
2
  • 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. Commented May 28, 2010 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. Commented May 28, 2010 at 11:18

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.