Change Data Capture in SQL Server 2008 Enterprise Edition looks great. It captures all changes which is what we need and it will save us from custom coding this.
A couple other things would go a long way towards an end-to-end solution. These are:
1) Ability to see all changes across all tables - so maybe not with the data, but something like what type of change was made, to what table, and when. Is there a management view to get this? I appreciate that the CDC tables are custom making this less than trivial.
2) Some tool to view the CDC changes stored. Anyone know of third-party viewers?
-Krip
For item 1, you could use a SProc and Dynamic SQL to generate a result set that contains all tables and changes (by type and time) using the following tables...
cdc.change_tables
a database system table that contains a record for each table in the database with CDC enabled.
cdc.(CAPTURE_INSTANCE)_CD
this is the table created to hold the historical changes made to the source table when CDC is first enabled on the source table. the value for (CAPTURE_INSTANCE) can be found in the capture_instance column of the first table (cdc.change_tables)