Store Procedure to capture changes to tables/column

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.