7

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?

3

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.

  • Sorry, I probably wasn't clear enough in the question. What I'm trying to find is for a given Lsn, which cdc tables have a record – Greg Dec 11 '13 at 3:50
  • @Greg I Have updated my answer. – Kin Shah Dec 11 '13 at 4:23
  • I've added some more info to the question which might make it a bit clearer. We're trying to find a particular range of lsn's across the database when we have no idea where they are – Greg Dec 11 '13 at 5:46
  • 1
    Downvoters ... Please leave comment as to why the answer was down voted ? – Kin Shah Jul 22 '14 at 1:53
0

As mentioned in the question, we're trying to query multiple cdc tables and apply a similar process to all of them. The only way to do this is by querying each table individually (using the fn_cdc_get_all_changes_dbo_tablename function), there is no central place to check what has changed.

  • 1
    You should just use dynamic tsql on sys.tables filtered by is_tracked_by_cdc and use the function to query what has changed. – Kin Shah Dec 30 '17 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.