Since database are usually designed in development environments, migrating changes over to other environments (e.g. QA and production) is not a trivial task. This is because SQL Server does not have any built-in tools to compare database schemas.
There are quite a few third-party tools that would make your life easier, most notably, Red Gate's SQL Compare (reasonably priced around $300).
At times, buying a commercial tool is not really an option, so one must resort to googling for free tools. There are three such tools that I am aware of:
- SQLDBDiff by SQLDBTools
A very decent tool that comes in both freeware and shareware versions. Freeware version is not badly crippled; only advanced features such as multi-database comparison, data content comparison, etc. are disabled.
- Database Schema Comparison Utility
This is a Code Project article that comes with C# source code of a schema comparison utility. The utility itself is pretty bare-bone, but gets the job done.
- StarInix Free Database Compare 2.0
I have not used this tool, but from the advertised feature list, it looks pretty good. Most notably, in addition to SQL Server, this tool works with Access and MySQL databases.