What tables are affected by Change Data Capture
First you have to enable CDC at database level.
USE database_name
GO
EXEC sys.sp_cdc_enable_db
GO
Then you have to enable CDC at table level
USE database_name
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'table1',
@role_name = NULL
Below query will tell you what tables have cdc enabled
SELECT [name], is_tracked_by_cdc
FROM sys.tables
update:
What I'm trying to find is for a given Lsn, which cdc tables have a record
I guess this is not relevant, as when enabling a CDC, you will have
__$start_lsn
__$end_lsn
__$seqval
__$operation
__$update_mask
The __$start_lsn
and __$end_lsn
will correspond to the table itself that you have enabled CDC ON.
I dont see a need where you have to do a table search based on LSN to find out the table name - unless I am not comprehending your question.
You can even look up in the system tables
cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping => Returns one row for each transaction having rows in a change table. This table is used to map between log sequence number (LSN) commit values and the time the transaction committed.
dbo.systranschemas
Read up : An Introduction to SQL Server 2008 Change Data Capture by Brad McGehee and Introduction to Change Data Capture (CDC) in SQL Server 2008 by Pinal Dave.