Store Procedure to capture changes to tables/column

Thanks. Well appreciated.

I need help to develop a stored procedure that can capture changes to all tables/columns

And now you are expecting a "universal-I-can-do-anything" solution without giving any usefull informations about your database/tables??

Impossible!!!

You have to develop an own solution depending on your database/tables design.

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.)

Here's a stored procedure example to capture changes in all tables/columns in a CDC-enabled SQL database and store those changes into another table.

CREATE PROCEDURE CaptureChanges
AS
BEGIN
    DECLARE @tableName NVARCHAR(256);
    DECLARE @schemaName NVARCHAR(256);
    DECLARE @cdcQuery NVARCHAR(MAX);
    DECLARE @changeTable NVARCHAR(256);

    DECLARE table_cursor CURSOR FOR
    SELECT schema_name(schema_id) AS schemaName, name AS tableName
    FROM sys.tables
    WHERE is_tracked_by_cdc = 1;

    OPEN table_cursor;
    FETCH NEXT FROM table_cursor INTO @schemaName, @tableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cdcQuery = '
        SELECT 
            ''' + @schemaName + '.' + @tableName + ''' AS TableName,
            CASE
                WHEN __$operation = 2 THEN ''INSERT''
                WHEN __$operation = 3 THEN ''DELETE''
                WHEN __$operation = 4 THEN ''UPDATE''
            END AS Operation,
            __$start_lsn AS LSN,
            __$seqval AS SeqVal,
            __$update_mask AS UpdateMask,
            ' + @tableName + '.*,
            CURRENT_TIMESTAMP AS ChangeDate
        FROM cdc.fn_cdc_get_all_changes_' + @tableName + '(sys.fn_cdc_get_min_lsn(''' + @schemaName + '_' + @tableName + '''), sys.fn_cdc_get_max_lsn(), ''all'')';

        SET @changeTable = @schemaName + '_' + @tableName + '_Changes';

     
        IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @changeTable)
        BEGIN
            EXEC('CREATE TABLE ' + @changeTable + ' ( 
                ChangeID INT IDENTITY(1,1) PRIMARY KEY,
                TableName NVARCHAR(256),
                Operation NVARCHAR(10),
                LSN VARBINARY(10),
                SeqVal VARBINARY(10),
                UpdateMask VARBINARY(10),
                ChangeDate DATETIME,
                ' + @tableName + '.* 
            )');
        END

        EXEC('INSERT INTO ' + @changeTable + ' ' + @cdcQuery);

        FETCH NEXT FROM table_cursor INTO @schemaName, @tableName;
    END

    CLOSE table_cursor;
    DEALLOCATE table_cursor;
END;

This stored procedure does the following:

1. It loops through all the CDC-enabled tables.
2. Constructs a query to get all changes for each table.
3. Checks if a change table exists for each table, and creates it if it doesn't.
4. Inserts the changes into the respective change table.

You'll need to adapt this procedure based on your specific requirements and the structure of your tables.