Create Audit Trail from SQL CDC

Hi @Ajeet Singh , welcome to Microsoft Q&A forum.

As I understand you want to capture the CDC tables records in the form of old values and new values for changed columns.

Actually there is no direct way of doing it as CDC enabled tables return 2 rows one for old value and one for new value and we need to do PIVOT to change the rows to column value. However based on below article I added one extra column in the script which could achieve something similar to it:

USING UNPIVOT WITH CDC FUNCTIONS TO GET UPDATED COLUMNS LIST

This looks a little complex but if we read through it, it is following same pattern of getting older values and newer values and then pivoting it. Below is an example of tables with 3 columns but you can keep on adding the columns based on table. Again this is limited to one table a time.

DECLARE @from_lsn binary(10), @to_lsn binary(10);    
SET @from_lsn = sys.fn_cdc_get_min_lsn('SalesLT_ProductModel');    
SET @to_lsn   = sys.fn_cdc_get_max_lsn();    
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 ('SalesLT_ProductModel','Name'),__$update_mask) = 1) THEN CAST([Name] as sql_variant)   
 ELSE NULL END AS [Name],  
    CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('SalesLT_ProductModel','ProductModelID'),__$update_mask) = 1) THEN CAST ([ProductModelID] as sql_variant)   
 ELSE NULL END AS [ProductModelID],  
    CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('SalesLT_ProductModel','ModifiedDate'),__$update_mask) = 1) THEN CAST ([ModifiedDate] as sql_variant)   
 ELSE NULL END AS [ModifiedDate]  
    FROM [cdc].[fn_cdc_get_all_changes_SalesLT_ProductModel](@from_lsn, @to_lsn, N'all update old')  
    WHERE __$operation = 3)as t1  
    UNPIVOT (old_value FOR column_name IN ([Name], [ProductModelID],[ModifiedDate]) ) as unp  ) as up_b   
 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 ('SalesLT_ProductModel','Name'),__$update_mask) = 1) THEN CAST([Name] as sql_variant)   
 ELSE NULL END AS [Name],  
    CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('SalesLT_ProductModel','ProductModelID'),__$update_mask) = 1) THEN CAST ([ProductModelID] as sql_variant)  
 ELSE NULL END AS [ProductModelID],  
    CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('SalesLT_ProductModel','ModifiedDate'),__$update_mask) = 1) THEN CAST ([ModifiedDate] as sql_variant)  
 ELSE NULL END AS [ModifiedDate]  
    FROM [cdc].[fn_cdc_get_all_changes_SalesLT_ProductModel](@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 ([Name], [ProductModelID],[ModifiedDate]) ) as unp) as up_a  
 ON up_b.__$start_lsn = up_a.__$start_lsn AND up_b.column_name = up_a.column_name  

Output:

Please let us know if this helps or else we can discuss further.

----------

If answer is helpful please please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!