Enabling SQL Server Change Data Capture on all tables with a primary key

I decided to play around with SQL Server’s new Change Data Capture feature. Here’s a script I use to enable change data capture on all tables within a database:

DECLARE @sql TABLE (line int identity(1,1), script nvarchar(max))

INSERT INTO @sql (script)
UNION ALL SELECT 'EXEC sys.sp_cdc_enable_db'

INSERT INTO @sql (script)
SELECT 'EXEC sys.sp_cdc_enable_table '
	+ '@source_schema = N'''+s.name+''', '
	+ '@source_name = N'''+t.name+''', '
	+ '@role_name = N''db_cdcadmin'', ' -- role that users must belong to in order to see changes
	+ '@supports_net_changes = 1' -- only returns one row for a series of changes
FROM sys.tables t, sys.schemas s
WHERE t.[type] = 'U' AND t.schema_id = s.schema_id
AND EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = t.object_id AND is_primary_key = 1)

SELECT script FROM @sql ORDER BY line

And here’s the script I use to report on the changes. I typically only care about the NET changes and not so much about the individual changes.

-- Press [CTRL]+[SHIFT]+[M] to fill in the template parameters...

-- grab the min and max LSN
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn = sys.fn_cdc_get_min_lsn('_')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

-- display ALL the changes
SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS LSN_DATE,  
FROM cdc.fn_cdc_get_all_changes__(@from_lsn, @to_lsn, N'all');

-- display only the NET change
SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS LSN_DATE,  
FROM cdc.fn_cdc_get_net_changes__(@from_lsn, @to_lsn, N'all');

Leave a Reply

Your email address will not be published. Required fields are marked *

1 + = five

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>