Store Procedure to capture changes to tables/column

Enable CDC on the Database and Tables

If your database is not yet CDC–enabled, run the following on your database (replace YourDatabase with your name):

USE YourDatabase;
GO
EXEC sys.sp_cdc_enable_db;
GO

-------------------

Then, for each table that you wish to capture, run (adjusting the schema and table name):

EXEC sys.sp_cdc_enable_table 
    @source_schema = N'dbo', 
    @source_name   = N'YourTable', 
    @role_name     = NULL;  -- or specify a role if needed
GO

*Repeat for all tables you want audited.

------------------------------

Create an Audit Table

See the below example, we create a table named AuditLog that will store:

The source table (including schema)
The type of operation (INSERT, UPDATE, DELETE)
The date/time the change was captured
The “old” values (for deletes and update–before images)
The “new” values (for inserts and update–after images)


CREATE TABLE dbo.AuditLog (
    AuditLogID     INT IDENTITY(1,1) PRIMARY KEY,
    SourceTable    NVARCHAR(128),
    Operation      NVARCHAR(10),
    ChangedDateTime DATETIME2,
    OldValues      XML NULL,
    NewValues      XML NULL
);
GO


------------------------------------------

Create the Stored Procedure to Capture CDC Changes

The following stored procedure (named dbo.CaptureAllCDCChanges) accepts two parameters: a starting LSN (@from_lsn) and an ending LSN (@to_lsn). (Typically you would record the last processed LSN after each run and use that as the next run’s starting LSN.) The procedure then uses a cursor to loop over all CDC–enabled tables (information available from the system table cdc.change_tables). For each table it builds and executes a dynamic SQL statement that calls the appropriate CDC function (for example, if the capture instance is named YourTable, then the function is called cdc.fn_cdc_get_all_changes_YourTable).

CREATE PROCEDURE dbo.CaptureAllCDCChanges
    @from_lsn varbinary(10),
    to_lsn   varbinary(10)
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables to hold the schema name, table name, and capture instance.
    DECLARE @schema_name    sysname,
            @table_name     sysname,
            @capture_instance sysname,
            @SQL            NVARCHAR(MAX);

    -- Get a list of all CDC-enabled tables from the CDC system table.
    DECLARE cdc_cursor CURSOR FAST_FORWARD FOR
        SELECT sch.name      AS SchemaName,
               tab.name      AS TableName,
               ct.capture_instance
        FROM cdc.change_tables ct
            INNER JOIN sys.tables tab ON ct.[object_id] = tab.[object_id]
            INNER JOIN sys.schemas sch ON tab.[schema_id] = sch.[schema_id];

    OPEN cdc_cursor;
    FETCH NEXT FROM cdc_cursor INTO @schema_name, @table_name, @capture_instance;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --
        -- Build a dynamic SQL statement that:
        --   1. Calls the CDC function for this table/capture instance for changes between @from_lsn and to_lsn.
        --   2. Inserts the results into dbo.AuditLog.
        --
        -- For CDC, the __$operation column returns:
        --    1 = DELETE (the row’s “before” image)
        --    2 = INSERT (the row’s “after” image)
        --    3 = UPDATE (the row’s “before” image)
        --    4 = UPDATE (the row’s “after” image)
        --
        -- In our simple example, we log:
        --   - For INSERT: NewValues (the full inserted row)
        --   - For DELETE: OldValues (the full deleted row)
        --   - For UPDATE: We log one row for the before image (operation=3) and one for the after image (operation=4)
        --     and mark both as 'UPDATE'. (A more advanced solution would pair these rows.)
        --
        SET @SQL = N'
            INSERT INTO dbo.AuditLog (SourceTable, Operation, ChangedDateTime, OldValues, NewValues)
            SELECT 
                ''' + @schema_name + '.' + @table_name + ''',
                CASE 
                    WHEN __$operation = 1 THEN ''DELETE''
                    WHEN __$operation = 2 THEN ''INSERT''
                    WHEN __$operation IN (3,4) THEN ''UPDATE''
                    ELSE ''UNKNOWN'' 
                END AS Operation,
                GETDATE() AS ChangedDateTime,
                CASE 
                    WHEN __$operation = 3 THEN (SELECT t.* FOR XML AUTO, ELEMENTS)
                    WHEN __$operation = 1 THEN (SELECT t.* FOR XML AUTO, ELEMENTS)
                    ELSE NULL
                END AS OldValues,
                CASE 
                    WHEN __$operation = 2 THEN (SELECT t.* FOR XML AUTO, ELEMENTS)
                    WHEN __$operation = 4 THEN (SELECT t.* FOR XML AUTO, ELEMENTS)
                    ELSE NULL
                END AS NewValues
            FROM cdc.fn_cdc_get_all_changes_' + QUOTENAME(@capture_instance) + 
            '(@from_lsn, to_lsn, ''all'') AS t;
        ';

        -- Execute the dynamic SQL with parameter substitution.
        EXEC sp_executesql 
            @SQL, 
            N'@from_lsn varbinary(10), to_lsn varbinary(10)',
            @from_lsn = @from_lsn, 
            to_lsn   = to_lsn;

        FETCH NEXT FROM cdc_cursor INTO @schema_name, @table_name, @capture_instance;
    END;

    CLOSE cdc_cursor;
    DEALLOCATE cdc_cursor;
END;
GO


NOTE: (LSN parameters)    The CDC functions expect a “from LSN” and “to LSN” of type varbinary(10). In production you would record (in a separate table or variable) the last LSN processed so that each run only captures new changes. For example, you might call

DECLARE @startLSN varbinary(10) = (SELECT LastLSN FROM dbo.CDCStatus);
DECLARE @endLSN   varbinary(10) = sys.fn_cdc_get_max_lsn();
EXEC dbo.CaptureAllCDCChanges @from_lsn = @startLSN, to_lsn = @endLSN;
-- Then update dbo.CDCStatus with @endLSN.

-----------------------------

CDC records updates as two rows (one with operation = 3 [before image] and one with operation = 4 [after image]).


In the above sample we simply log each CDC row as a separate audit row. If you need to combine (pair) the before and after images for an update, you would need to write additional logic (for example, using a common table expression or temporary table keyed on the CDC sequence value) so that each update appears as a single record with both old and new values.


Running the Procedure

You would call the procedure by supplying the appropriate LSN range.

DECLARE @from_lsn varbinary(10) = sys.fn_cdc_get_min_lsn('YourCaptureInstance'); 
DECLARE to_lsn   varbinary(10) = sys.fn_cdc_get_max_lsn();

EXEC dbo.CaptureAllCDCChanges @from_lsn, to_lsn;

In a production scenario you would:

Store the “last processed” LSN (for example, in a table such as dbo.CDCStatus).
Use that LSN as the starting point for the next run.
Schedule the procedure (for example, via SQL Server Agent) to run periodically.



(This solution provides a generic framework that you can further refine (for example, to log only the changed columns) to meet your requirements.)