Maintaining stored procedures in source control

Question

How do you guys maintain your stored procedures? I'd like to keep versions of them for a few different reasons.

I also will be setting up cruisecontrol.net and nant this weekend to automate builds.

I was thinking about coding something that would generate the create scripts for all tables/sprocs/udf/xml schemas in my development database. Then it would take those scripts and update them in source control every couple hours.... Ideally, I'd like to make this some sort of plugin/module for cruisecontrol.net.

Any other ideas?

Answer 1

We use Red Gate's SQL Toolbelt at work for this very purpose. Works like a charm.

http://www.red-gate.com/

Answer 2

Every object, stored procedure or otherwise, and schema change is handled by a script (text file) under subversion control. So just like any other file in your project. Changes to object/schema are made by executing those scripts against the relevant database. Our build process aggregates those into one big script for convenience. And schema change scripts are written in a way so that they can be rerun without warnings/errors ( if not exists... exec...)

I would advise against editing table definitions or creating objects directly in the database using query tools, then after the fact trying to extract those changes into scripts.

Instead make the changes to your dev database the same way as you will in qa/production, using those version-controlled scripts.

Answer 3

@eckesicle The SQL Toolbelt is almost 2 Grand! Can any of ya'll who actually use it tell me more about how you convinced your DEV / Project Manager that you absolutely must have that tool for your projects ?!