How to know when a table was removed from Change Data Capture(CDC) or added to it?

SQL Server 2008 has Change Data Capture feature that allows to capture changes made in the ta开发者_运维百科ble, such as insert, delete or update rows.

I have noticed that a table was excluded from Change Data Capture (CDC) which brought lots of problems.

Is there a way to find out when a table was removed from CDC or even who removed the table?

Is there some kind of log of CDC where such information is available?

You could check the Reports / Standard Reports / Schema changes history, I would think it would get picked up there since the capture tables probably would be dropped, but that data comes from the default trace, so it may not go back far enough for you.

CDC keeps track of DDL changes made on the monitored tables: http://msdn.microsoft.com/en-us/library/bb522553(v=sql.110).aspx and http://msdn.microsoft.com/en-us/library/bb510681(v=sql.110).aspx

Also select modify_date from sys.tables where object_id = object_id('dbo.yourtablename') will give you the time and date the table's DDL was last changed. Unless you've got monitoring installed on all DDL changes there's -to my knowledge- no way to get all changes made to your table's definitions.