There are several tools that you can use to help track database changes, so that come release you avoid an error or omission in the database scripts that brings your system crashing to its knees. However keeping change scripts up to date requires a lot of discipline. Tools such as Redgate’s SQL Compare can validate that one database schema matches another and produce change scripts to align them. This is all useful stuff, but some operations such as renaming tables and columns cannot be picked up using this technique. As a result I mainly use these types of tool to verify changes have been successfully applied. You are still left with the problem that a lack of discipline can mean cancelling the release. There is another method you can use in conjunction to the comparison tools however. When I first heard about the ability in SQL 2005 to capture DDL [Data Definition Language] changes I immediately thought about how I could apply the feature to the problem of tracking DB changes.
I got hold of an example script from here and modified it like so: