How to know which column's value is changed in CDC

Question

I am using CDC feature in sql 2012 and lets suppose i have a table name as Employee and also have some records in it . Suppose someone changed Last Name in employee's table . Then there goes 2 entries in CDC table , have both records before change and after change . I want to know that which column is changed .

I am following this tutorial

Answer 1

I think what you're looking for is column tracking. You'll need to utilize the CHANGETABLE function to get what you want.

Column tracking information appears in the SYS_CHANGE_COLUMNS column that is returned by the CHANGETABLE(CHANGES …) function.

Check out this MSDN article for more information.

Answer 2

A better option. This how to tell if the LastName value has changed using a CDC function

        SELECT 
        sys.fn_cdc_map_lsn_to_time(x.__$start_lsn) AS ChangeDateTime
    ,   x.__$start_lsn
    ,   x.__$operation
    ,   x.__$update_mask
    ,   x.YourPrimaryKeyColumn
    ,   x.LastName
    ,   sys.fn_cdc_has_column_changed ('YourCDCInstanceName', 'LastName', x.__$update_mask) LastNameHasChanged
    FROM cdc.fn_cdc_get_all_changes_YourCDCInstanceName(@from_lsn, @to_lsn, N'all') x

Check the documentation for more info.