Storing the drop/create scripts for stored procedures and, indeed, every other type of database object in a source control/configuration management system should be standard practice for any development shop.  You do it without question for application code, why not for database code?

And there's the rub...

Database code isn't like normal application code.  You can't just build a brand new database and replace production with it.

But what if you had a magic black box that could analyse the individual drop/create scripts under source control for changes and apply those intelligently to a production database with no loss of data?

This was the question we aimed to solve three years ago and the 'black box solution' is now a product called DB Ghost (http://www.dbghost.com).

To release your latest set of code simply label it all in your SCS and extract it to disk.  DB Ghost will then:

1. Build a brand new database from the source scripts.  This ensures that a) no syntax errors exist and b) no dependency errors have been introduced.  This is the equivalent of a daily build but for databases.

2. Compare a target database to the newly built source database and modify the target to match it EXACTLY, no more, no less.

What you are effectively left with is a target database that matches a known, labelled set of source.

The benefits of this approach are many but the main one is that developers work with check out/check in for ALL databases objects just as they do for other application code.  This means that our approach is perfectly aligned with configuration management best principles as it deals with source code.

There is no other tool available today that does this.

The only downside? We need to feed our families so we charge for it.

How much is your time worth and how much of it do you spend on troubleshooting deployment and script collisions? 

DB Ghost could make those issues go away forever.