Whilst I have found RedGate's SQLCompare tool useful for deploying schema changes in the past, the problem it has is that is a tool and will never be able deal with the level of complexity that a human being can. As with all script generating tools, eventually it comes across something that it can't do 100% safely so it ends up throwing a warning or doing something drastic like dropping and recreating a table.

In my blog[/url], I've written about a method using Visual Studio Database Projects that combines user written scripts, for data and schema changes, with automated scripting of changes to programmability objects (stor procs, functions...).

The upside of this method is that it uses plain old visual studio solution files, so integrates seamlessly with Team Foundation Server. TFS has all sorts of funky Continuous Integration options to play with.

Warning: Hack alert.