CDC Detailed information

--First SP will collect the information with respect to columns during DML operation.

 

CREATE Procedure [cdc].[columntracking]  

( 

@TableName VarChar(100)   

) 

AS begin 

 

Declare @SQL1 VarChar(1000) 

Declare @Join1 VarChar(1000) 

Declare @Join2 VarChar(1000) 

 

SELECT @SQL1='select [__$update_mask] , 

        ( SELECT    CC.column_name +'','' 

          FROM      cdc.captured_columns CC 

                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id] 

          WHERE     capture_instance = ''dbo_' 

           

 select  @Join1=''' 

          AND sys.fn_cdc_is_bit_set(CC.column_ordinal, 

                                              PD.__$update_mask) = 1 

        FOR 

          XML PATH('''') 

        ) AS changedcolumns 

     

FROM    cdc.dbo_' 

 

select  @Join2='_CT PD' 

 

 

 

select @SQL1=@SQL1+@TableName+@Join1+@TableName+@Join2 

 

Exec ( @SQL1) 

 

End  

 

Go

--Second SP will give us detailed information such as Time,DML operation,LSN,Translated update_mask known as columnchanged  etc for auditing purposes.

  

Create Procedure [cdc].[CDCAuditing]    

(   

@TableName VarChar(100)     

)   

AS begin  

 

create table #columntracking 

( 

updatemask varchar(1000), 

columnchanged varchar(max) 

) 

 

Insert #columntracking 

Exec [cdc].[columntracking] @TableName 

 

 

 

Declare @SQL VarChar(1000)   

Declare @Join varchar(1000)    

   

SELECT @SQL = 'SELECT distinct(TM.tran_begin_time ) ,  

      case [__$operation]   

      when 1 Then ''Delete''   

      when 2 Then ''Insert''   

      when 3 Then ''Before update''   

      when 4 Then ''After update''   

      End as [__$operation]   

      ,TM.tran_begin_time   

      ,TM.tran_end_time   

      ,M.[__$update_mask] 

      ,CT.columnchanged 

      ,M.*  

        

  FROM [cdc].[dbo_'   

   

select @join= '_CT] as M inner join cdc.lsn_time_mapping as TM  

on  TM.start_lsn=M.[__$start_lsn] 

Left outer join #columntracking as CT 

on CT.updatemask=M.[__$update_mask] order by TM.tran_begin_time ,M.__$operation'   

 

SELECT @SQL = @SQL + @TableName +@join    

   

Exec ( @SQL)   

   

End