Master Viewer for Change Data Capture

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. The开发者_JAVA技巧se 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)