Does anyone know a tool that automatically compares the structure of a database and gives a report of what was changed? If it can be integrated into a CruiseControl, even better.
By structure I also mean functions and stored procedures. Which is probably not clear at first sight ;-)
The idea was to integrate that into a cruise control server
SQL Compare by Redgate is purportedly good at this.
Failing that you can get a good idea of a schema compare by comparing the results of queries:
select * from information_schema.columns
select * from information_schema.tables
SQL Effects Clarity is very good at comparing two Databases, and it's free (it has a paid version that allows you to do a bit more as well)
For integrating into CC if the output of the tool is plaintext you can publish it as an artifact. If the output is .xml you can merge it into the log and write an xsl that will include it in the email & web pages.
I use Redgate SQL Compare (link). It is about $400 per seat but is well worth the price
ApexSQL Diff and ApexSql Data Diff. They work great for me. Also, I've used the Redgate tools in the past with good success.
I would definitely go with AdeptSQL if you're using MSSQL. It's the least good looking but the most talented db compare tool amongst the ones I've tried. It can compare both the structure and the data. It tells you which tables exist on one db but does not exist on the other, compares the structure and data of the common ones and it can produce the script to synchronize the two. It's not free but has a 30 day trial (as far as I can remember)