Automated deployments of stored-procs is relatively easy, but anything that involves a table schema change for example kind of makes the whole approach a bit complex and prone to problems. Also, it becomes much harder if you are building routines that need to be created in a specific order- we've toyed with creating dependencies but never got it working satisfactorily.

More useful for us in our small shop was detecting unauthorised/unexpected changes to our database objects, and providing a good audit of objects altered to tie into our change control process.

Using SMO we've been able to version most objects quite easily and build a daily source copy and an associated job to compare the output to a VC'd baseline, noting differences.

It's also made capturing changes between a development environment and production environment a lot easier- script both to two folders and run a winmerge or similar comparison tool to see what ammendments have been made- although as noted above, syncing the changes almost always is easier to handle the "long" way for our relatively simple db.