Rotating Header Image

The Change Data Capture (CDC) Easy Button

I’ve been working alot with CDC the last little while and found these couple of scripts very useful. These scripts are handy in a DEV type of environment… you should be alot pickier about what you use CDC for but if you’re looking to play around and get your hands dirty then these are the scripts for you. 🙂

The first script:

  • Checks to see if CDC is enabled
  • Enabled CDC
  • Creates an “Audit” schema
  • Add all tables to CDC
  • Create a useful CDC view for each table in the “Audit” schema
  • Change the default CDC retention period

The second script tears it all down:

  • Drops the views
  • Disables the tables from CDC
  • Drops the “Audit” Schema
  • Disables CDC from the database

FIRST SCRIPT

USE DB_NAME
GO
SET NOCOUNT ON
GO
DECLARE @is_cdc_enabled int
DECLARE @cdc_schema_id int
DECLARE @Role_Name nvarchar(100)
DECLARE @CDC_Retention int
DECLARE @SQL_STMT nvarchar(2000)
DECLARE @RowCount int
DECLARE @CDC_TableName nvarchar(256)
DECLARE @ColumnList nvarchar(2000)
 
SELECT	@Role_Name = NULL
SELECT	@CDC_Retention = 13148730 -- 25 years in minutes
 
/*
**********************************************
		ENABLE AND CONFIGURE CDC
**********************************************
*/
SELECT	@is_cdc_enabled = is_cdc_enabled
FROM	sys.databases
WHERE	[name] = DB_NAME()
 
IF	@is_cdc_enabled != 1
BEGIN
	EXEC sys.sp_cdc_enable_db
END
 
SELECT @SQL_STMT = 'CREATE SCHEMA [Audit] AUTHORIZATION [dbo]'
 
IF NOT EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'Audit')
BEGIN
	EXEC sp_executesql @SQL_STMT
END
 
/*
**********************************************
		ADD TABLES TO CDC
**********************************************
*/
 
SELECT	@cdc_schema_id = [schema_id]
FROM	sys.schemas
WHERE	[name] = 'cdc'
 
SELECT	[name] as 'CDC_TableName'
INTO	#UserTables
FROM	sys.objects
WHERE	type = 'U' and
		is_ms_shipped != 1 and
		[schema_id] != @cdc_schema_id
 
SELECT	@RowCount = COUNT(1)
FROM	#UserTables
 
WHILE	@RowCount != 0
BEGIN
	SELECT TOP 1 @CDC_TableName = CDC_TableName
	FROM	#UserTables
 
	SELECT @SQL_STMT = 'EXEC sys.sp_cdc_enable_table
      @source_schema = ''dbo'',
      @source_name   = ''' + @CDC_TableName + ''',
      @role_name     = null;'
 
	EXEC sp_executesql @SQL_STMT
 
	SELECT	@ColumnList = STUFF(COLUMN_NAME, 1, 1, '')
	FROM	sys.tables t CROSS APPLY  (SELECT	',' + name AS [text()]
									   FROM		sys.columns c
									   WHERE    c.object_id = t.object_id
									   FOR XML PATH('')) o (COLUMN_NAME)
	WHERE OBJECT_NAME(t.[object_id]) = @CDC_TableName
 
	SELECT	@SQL_STMT = 'CREATE VIEW Audit.vw_' + @CDC_TableName +
			' AS     SELECT A.__$start_lsn as ''StartLSN'',
     B.tran_begin_time as ''TransactionStartTime'',
      B.tran_end_time as ''TransactionEndTime'',
      CASE
       WHEN A.__$operation = 1 THEN ''DELETE''
       WHEN A.__$operation = 2 THEN ''INSERT''
       WHEN A.__$operation = 3 THEN ''UPDATE_BEFORE''
       WHEN A.__$operation = 4 THEN ''UPDATE_AFTER''
      END as ''Operation'', ' +
      @ColumnList +
      ' FROM cdc.dbo_' + @CDC_TableName + '_CT A INNER JOIN
  cdc.lsn_time_mapping B ON A.__$start_lsn = B.start_lsn'
 
	EXEC sp_executesql @SQL_STMT
 
	DELETE
	FROM #UserTables
	WHERE	CDC_TableName = @CDC_TableName
 
	SELECT @RowCount = @RowCount - 1
 
END
 
DROP TABLE #UserTables
 
/*
**********************************************
		CHANGE CDC RETENTION
**********************************************
*/
 
EXECUTE sys.sp_cdc_change_job
        @job_type = N'cleanup',
        @retention = @CDC_Retention

SECOND SCRIPT

/*
**********************************************
		TEAR IT DOWN SCRIPT
**********************************************
*/
USE DB_NAME
GO
DECLARE @Audit_schema_id int
DECLARE @RowCount int
DECLARE @ViewName nvarchar(256)
DECLARE @TableName nvarchar(256)
DECLARE @SQL_STMT nvarchar(2000)
 
SELECT	@Audit_schema_id = [schema_id]
FROM	sys.schemas
WHERE	[name] = 'Audit'
 
SELECT	name as 'ViewName'
INTO	#AuditViews
FROM	sys.views
WHERE	schema_id = @Audit_schema_id
 
SELECT	@RowCount = COUNT(1)
FROM	#AuditViews
 
WHILE	@RowCount != 0
BEGIN
	SELECT TOP 1 @ViewName = ViewName
	FROM	#AuditViews
 
	SELECT @SQL_STMT = 'DROP VIEW Audit.' + @ViewName
 
	EXEC sp_executesql @SQL_STMT
 
	DELETE
	FROM #AuditViews
	WHERE	ViewName = @ViewName
 
	SELECT @RowCount = @RowCount - 1
 
END
 
DROP TABLE #AuditViews
 
SELECT	[name] as 'TableName'
INTO	#CDC_TrackedTables
FROM	sys.tables
WHERE	is_tracked_by_cdc = 1
 
SELECT	@RowCount = COUNT(1)
FROM	#CDC_TrackedTables
 
WHILE	@RowCount != 0
BEGIN
	SELECT TOP 1 @TableName = TableName
	FROM	#CDC_TrackedTables
 
	SELECT @SQL_STMT = 'EXEC sys.sp_cdc_disable_table
      @source_schema = ''dbo'',
      @source_name   = ''' + @TableName + ''',
      @capture_instance     = N''All'';'
 
	EXEC sp_executesql @SQL_STMT
 
	DELETE
	FROM #CDC_TrackedTables
	WHERE	TableName = @TableName
 
	SELECT @RowCount = @RowCount - 1
 
END
 
DROP TABLE #CDC_TrackedTables
 
SELECT @SQL_STMT = 'DROP SCHEMA [Audit]'
 
IF EXISTS(SELECT 1 FROM sys.schemas WHERE name = 'Audit')
BEGIN
	EXEC sp_executesql @SQL_STMT
END
 
EXEC sys.sp_cdc_disable_db

There ya go… now you don’t have a reason not to get in there and play around 🙂

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

7 Comments

  1. Wes Brown says:

    Sweet! I’m gearing up to use CDC and kill off all our history triggers, this will save me some sweat equity.

  2. Great! 🙂 Glad to of helped

    CDC is pretty cool stuff… the more I play with it the more I like it.

  3. Thanks to say for posting this blog. We have to get new collection of news from your end. All the best for you best support. Keep updataing your blog. This is really nice job…

  4. Gurb says:

    This is great but how do you disable CDC without losing the change tracking tables in the cdc schema?

  5. Michael says:

    Hey Colin,
    you might want to add to this and your backup blogs that when restoring a database that has CDC enabled you need to add “WITH KEEP_CDC” to your restore statement. else you lose all the audit data, drops the audit tables and turns off CDC on the target!!!

  6. After using Slate for just a couple days, my early impression is that it’s similar to Storehouse, another creative layout app (for lack of a better term) that I’ve reviewed.

  7. sqlbarbarian says:

    fantastic… really helped me get CDC running for some dev work. thanks for the hard work

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.