Sql-server – Use of NOLOCK with CDC capture tables

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.

Input (hopefully stored procedure parameters):

DECLARE @table1 NVARCHAR(513) = N'dbo.Users',
        @table2 NVARCHAR(513) = N'dbo.DataObjects',
        @ID     INT = 10;

Code:

DECLARE @InstanceName1 NVARCHAR(513),
        @InstanceName2 NVARCHAR(513),
        @Begin_LSN     BINARY(10),
        @End_LSN       BINARY(10);

SELECT @InstanceName1 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table1,1)
    AND s.name = PARSENAME(@table1,2);

SELECT @InstanceName2 = c.capture_instance
  FROM cdc.change_tables AS c
  INNER JOIN sys.tables AS t
  ON c.[source_object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE t.name = PARSENAME(@table2,1)
    AND s.name = PARSENAME(@table2,2);

SELECT @Begin_LSN = sys.fn_cdc_get_min_lsn(@InstanceName1), 
       @End_LSN   = sys.fn_cdc_get_max_lsn();

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT * 
  FROM cdc.fn_cdc_get_all_changes_' + @InstanceName2
  + '(@b, @l, ''ALL'') AS a 
  INNER JOIN cdc.fn_cdc_get_all_changes_' + @InstanceName1
  + '(@b, @l, ''ALL'') AS b 
  ON a.__$start_lsn = b.__$start_lsn
  WHERE a.ID = @ID;';
  ------^ guessing here

EXEC sp_executesql @sql, 
  N'@b BINARY(10), @l BINARY(10), @ID INT',
  @Begin_LSN, @End_LSN, @ID;