Enable CDC on All Tables

A script that generates the statements to enable change data capture on every table in the current database.

Published Jul 23, 2007

Need change data capture on every table in a database? This script generates the complete set of statements: enable CDC at the database level with sp_cdc_enable_db, then emit an sp_cdc_enable_table call for every user table. A commented-out block at the bottom does the reverse - generating sp_cdc_disable_table calls from cdc.change_tables.

SELECT 'USE master'
UNION ALL SELECT 'GO'
UNION ALL SELECT 'USE ' + db_name()
UNION ALL SELECT 'GO'
UNION ALL SELECT 'EXEC sys.sp_cdc_enable_db'
UNION ALL SELECT 'GO'

UNION ALL SELECT
	'EXEC sys.sp_cdc_enable_table @source_schema = N''' + schema_name(schema_id) + ''', @source_name = ''' + name  + ''', @role_name= NULL, @supports_net_changes = 1'
FROM
	sys.objects
WHERE
	type = 'U'
	AND is_ms_shipped = 0
GO

/*
--Disable
SELECT
	'EXEC sys.sp_cdc_disable_table @source_schema = N''' + schema_name(schema_id) + ''', @source_name = ''' + object_name(source_object_id) + ''', @capture_instance= ''dbo_' + object_name(source_object_id) + ''''
FROM
	cdc.change_tables
GO	
*/