What tables are affected by Change Data Capture

We're looking at using Change Data Capture in SQL 2012. We're trying to work out if there is an easy way to find out what tables have been changed by a given lsn. Is there an inbuilt function, or would we need to query each cdc table to see if the lsn is in there?

UPDATE to explain a bit more about what we're trying to do:

We're trying to build a process that looks at cdc data for all the tables in the database (think 100's). This process will run on a schedule and will know the last lsn that it processed. When it runs we want to do something with each of the cdc tables that have had a change since the last recorded lsn.

We could check the fn_cdc_get_all_changes_dbo_tablename(@lastlsn, @currentlsn, 'all'), but the problem with that is that we would have to run it 100's of times (once for each table). Is there a central place we can check?

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.

StackExchange Database Administrators Q#54813, answer score: 3