Using UNPIVOT with CDC Functions to get Updated Columns List

Posted in: Technical Track

Microsoft introduced Change Data Capture (CDC) technology in SQL Server 2008.  This technology captures DML (insert/update/delete) changes to a table.  After CDC is enabled for a database and a given table, one can use cdc.fn_cdc_get_all_changes_ function to query changes made to the table. cdc.fn_cdc_get_all_changes_ function returns all columns from the table, even when only one column was updated. It also returns __$update_mask column which is a bit mask that shows which columns were updated, but still – the whole row is returned. When calling this function with “all_update_old” option it returns two records for each update: one with “before update” values and one with “after update” values.  The challenge was to produce a “log” table with columns like this:

commit_timecolumn_nameold_valuenew_value

UNPIVOT operator helps us to produce desired result.

Here’s an example query:

SELECT sys.fn_cdc_map_lsn_to_time(up_b.__$start_lsn) as commit_time, up_b.column_name, up_b.old_value, up_a.new_value
FROM
	( SELECT __$start_lsn, column_name, old_value
	FROM (SELECT __$start_lsn,
	CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_1'),__$update_mask) = 1) THEN CAST([CDC_column_1] as sql_variant) ELSE NULL END AS [CDC_column_1],
	CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_2'),__$update_mask) = 1) THEN CAST ([CDC_column_2] as sql_variant) ELSE NULL END AS [CDC_column_2], ....
	FROM cdc.fn_cdc_get_all_changes_dbo_(@from_lsn, @to_lsn, N'all update old')
	WHERE __$operation = 3) as t1
	UNPIVOT (old_value FOR column_name IN ([CDC_column_1], [CDC_column_2], ...) ) as unp) as up_b -- before update
INNER JOIN
	(SELECT __$start_lsn, column_name, new_value
	FROM (SELECT __$start_lsn,
	CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_1'),__$update_mask) = 1) THEN CAST([CDC_column_1] as sql_variant) ELSE NULL END AS [CDC_column_1],
	CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_2'),__$update_mask) = 1) THEN CAST ([CDC_column_2] as sql_variant) ELSE NULL END AS [CDC_column_2], ....
	FROM cdc.fn_cdc_get_all_changes_dbo_(@from_lsn, @to_lsn, N'all') -- 'all update old' is not necessary here
	WHERE __$operation = 4) as t2
	UNPIVOT (new_value FOR column_name IN ([CDC_column_1], [CDC_column_2], ...) ) as unp ) as up_a -- after update
ON up_b.__$start_lsn = up_a.__$start_lsn AND up_b.column_name = up_a.column_name

email

Interested in working with Igor? Schedule a tech call.

9 Comments. Leave new

Hi Igor Raytsin,

Super.. THis is what exactly i needed. Thanks a lot for sharing this.

Reply

Cool… thanks

Reply

Hello… Can you help me to make this query run.
I know this might be a generic solution but it will be great if you can point me to the changes I have to make to make this work for me.

Reply

Hello Sashank,
I’m not sure what query you are referring.
Could you please rephrase your question.

Thank you,
– Igor

Reply

:)Thanks for replying…I was talking about the query int he example.. It doesnt work for me. are there any changes i have to do before i can get this working.

Reply

i got it running now… but with one small issue… any help will be great…

I just get the column names in the fields old_value and new_value… was hoping to see the values in the column that changed.

example.. if col1 has changed i just see col1 in all column_nmae,old_value,new_value….

Reply

You saved my day buddy.. thank you very much… :)

Reply

Hi
Thanks for this post. – really helped me

I’ve turned it into a stored proc which takes FROM DATE, TO DATE, DATABASE NAME and TABLE as variables and produces the result as above dynamically. No need to hard code your column names etc

CREATEPROCEDURE [dbo].[USP_audit_Changes] (
@Datefrom VARCHAR(10) = ‘2015-09-20’
,@DateTo VARCHAR(10) = ‘2015-09-25’
,@Database VARCHAR(255) = ‘BCMS_TestQA’
,@table VARCHAR(255) = ‘Branch’
)
AS
BEGIN

DECLARE @CaseStatement NVARCHAR(Max)
DECLARE @ColumnList NVARCHAR(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @ParamDefinition AS NVARCHAR(2000)

SET @CaseStatement = ”
SET @ColumnList = ”

SELECT @CaseStatement = @CaseStatement + ‘CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal (”dbo_’ + @Table + ”’,”’ + Column_Name + ”’),__$update_mask) = 1) THEN CAST(‘ + Column_name + ‘ as sql_variant) ELSE NULL END AS ‘ + Column_name + ‘,’
FROM information_schema.columns
WHERE Table_Name = @table

SET @CaseStatement = LEFT(@CaseStatement, LEN(@CaseStatement) – 1)

SELECT @ColumnList = @columnList + Column_Name + ‘,’
FROM information_schema.columns
WHERE Table_Name = @table

SET @ColumnList = LEFT(@ColumnList, LEN(@ColumnList) – 1)

SET @SQL = ‘SELECT sys.fn_cdc_map_lsn_to_time(up_b.__$start_lsn) AS commit_time

,up_b.Column_name
,up_b.old_value
,up_a.new_value
FROM (
SELECT *
FROM (
SELECT __$start_lsn
,’ + @CaseStatement + ‘
FROM cdc.dbo_’ + @table + ‘_CT
WHERE __$operation = 3 and sys.fn_cdc_map_lsn_to_time(__$start_lsn) Between ”’ + @DateFrom + ”’ and ”’ + @DateTo + ”’
) AS t1
UNPIVOT(old_value FOR column_name IN (‘ + @ColumnList + ‘)) AS unp
) AS up_b — before update
INNER JOIN (
SELECT __$start_lsn

,column_name
,new_value
FROM (
SELECT __$start_lsn
,’ + @CaseStatement + ‘
FROM cdc.dbo_’ + @table + ‘_CT
WHERE __$operation = 4 and sys.fn_cdc_map_lsn_to_time(__$start_lsn) Between ”’ + @DateFrom + ”’ and ”’ + @DateTo + ”’
) AS t2
UNPIVOT(new_value FOR column_name IN (‘ + @ColumnList + ‘)) AS unp
) AS up_a — after update
ON up_b.__$start_lsn = up_a.__$start_lsn
AND up_b.column_name = up_a.column_name’
SET @ParamDefinition = ‘@Datefrom DATETIME ,@DateTo DATETIME, @Database VARCHAR(255),@table Varchar(255)’

EXEC Sp_executesql @SQL
,@ParamDefinition
,@From
,@To
,@database
,@table
–SELECT (@SQL)
END

Reply

Excellent!

Thank you Matt for improving my code and sharing it with us.

Thanks,
– Igor

Reply

Leave a Reply

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