SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @RoleName NVARCHAR(128) = N'cdc_reader'; -- Database role granted read access to CDC change tables

DECLARE @SupportsNetChanges BIT = 0; -- 0 = store every change; 1 = store net changes only (requires a primary key/unique index)

DECLARE @DryRun BIT = 1; -- 1 = report only (no changes); 0 = apply changes

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE database_id = DB_ID() AND is_cdc_enabled = 1)

RAISERROR('CDC is not enabled on database [%s]. Enable it with sys.sp_cdc_enable_db before running this script.', 16, 1, DB_NAME());

RETURN;

END;

DECLARE @schema_name NVARCHAR(128);

DECLARE @table_name NVARCHAR(128);

DECLARE @capture_instance NVARCHAR(128);

DECLARE tables_for_cdc CURSOR LOCAL FAST_FORWARD FOR

SELECT DISTINCT

s.name AS schema_name,

t.name AS table_name,

ct.capture_instance

FROM sys.schemas AS s

INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id

INNER JOIN sys.columns AS c ON t.object_id = c.object_id

INNER JOIN sys.types AS ty ON c.system_type_id = ty.system_type_id

AND c.user_type_id = ty.user_type_id

LEFT JOIN cdc.change_tables AS ct ON t.object_id = ct.source_object_id

LEFT JOIN cdc.captured_columns AS cc ON ct.object_id = cc.object_id

AND c.name = cc.column_name

AND ty.name = cc.column_type

WHERE t.type = 'U'

AND s.name <> 'cdc'

AND t.is_ms_shipped = 0

AND cc.column_name IS NULL;

OPEN tables_for_cdc;

FETCH NEXT FROM tables_for_cdc INTO @schema_name, @table_name, @capture_instance;

WHILE @@FETCH_STATUS = 0

-- ==========================================================================

-- Branch 1: Table is not currently tracked by CDC -- enable it

-- ==========================================================================

IF @capture_instance IS NULL

BEGIN

SET @capture_instance = CONCAT(@schema_name, N'_', @table_name);

IF @DryRun = 1

BEGIN

PRINT CONCAT('DRY RUN -- Would ENABLE CDC on [', @schema_name, '].[', @table_name, '] as capture instance [', @capture_instance, ']');

END

ELSE

BEGIN

BEGIN TRY

EXEC sys.sp_cdc_enable_table

@source_schema = @schema_name,

@source_name = @table_name,

@role_name = @RoleName,

@supports_net_changes = @SupportsNetChanges,

@capture_instance = @capture_instance;

PRINT CONCAT('ENABLED CDC on [', @schema_name, '].[', @table_name, '] as capture instance [', @capture_instance, ']');

END TRY

BEGIN CATCH

PRINT CONCAT('ERROR enabling CDC on [', @schema_name, '].[', @table_name, ']: ', ERROR_MESSAGE());

END CATCH;

END;

END

-- ==========================================================================

-- Branch 2: Table is tracked but schema has drifted -- recycle the capture

-- instance and preserve historical change data

-- ==========================================================================

ELSE

BEGIN

IF @DryRun = 1

BEGIN

PRINT CONCAT('DRY RUN -- Would RECYCLE CDC capture instance [', @capture_instance, '] on [', @schema_name, '].[', @table_name, '] due to schema drift');

END

ELSE

BEGIN

DECLARE @backup_table NVARCHAR(256) = CONCAT(N'temp_', @capture_instance, N'_CT');

DECLARE @ct_table NVARCHAR(256) = CONCAT(@capture_instance, N'_CT');

DECLARE @old_columns NVARCHAR(MAX);

DECLARE @common_cols NVARCHAR(MAX);

DECLARE @sql NVARCHAR(MAX);

DECLARE @cdc_disabled BIT = 0;

-- Reset aggregation variables to avoid stale values from a prior iteration

SET @old_columns = NULL;

SET @common_cols = NULL;

BEGIN TRY

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

-- Step 1: Drop any leftover staging table from a prior failed run

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

SET @sql = N'IF OBJECT_ID(N''[dbo].' + QUOTENAME(@backup_table) + N''') IS NOT NULL DROP TABLE [dbo].' + QUOTENAME(@backup_table) + N';';

EXEC sp_executesql @stmt = @sql;

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

-- Step 2: Build the column list from the EXISTING CT table and

-- back up the change data into a staging table

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

SELECT @old_columns = STRING_AGG(QUOTENAME(col.name), N', ')

FROM sys.columns AS col

INNER JOIN sys.tables AS tbl ON col.object_id = tbl.object_id

INNER JOIN sys.schemas AS sch ON tbl.schema_id = sch.schema_id

WHERE sch.name = N'cdc'

AND tbl.name = @ct_table;

SET @sql = N'SELECT ' + @old_columns

+ N' INTO [dbo].' + QUOTENAME(@backup_table)

+ N' FROM [cdc].' + QUOTENAME(@ct_table) + N';';

EXEC sp_executesql @stmt = @sql;

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

-- Step 3: Disable CDC (drops the old capture instance)

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

EXEC sys.sp_cdc_disable_table

@source_schema = @schema_name,

@source_name = @table_name,

@capture_instance = @capture_instance;

SET @cdc_disabled = 1;

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

-- Step 4: Re-enable CDC (creates a new capture instance with the

-- current source-table column layout)

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

EXEC sys.sp_cdc_enable_table

@source_schema = @schema_name,

@source_name = @table_name,

@role_name = @RoleName,

@supports_net_changes = @SupportsNetChanges,

@capture_instance = @capture_instance;

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

-- Step 5: Determine the COMMON columns between the backup table

-- and the newly created CT table. This safely handles

-- both added columns (exist in new CT but not backup)

-- and removed columns (exist in backup but not new CT).

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

SELECT @common_cols = STRING_AGG(QUOTENAME(new_col.name), N', ')

FROM sys.columns AS new_col

INNER JOIN sys.tables AS new_tbl ON new_col.object_id = new_tbl.object_id

INNER JOIN sys.schemas AS new_sch ON new_tbl.schema_id = new_sch.schema_id

INNER JOIN (

SELECT col.name

FROM sys.columns AS col

INNER JOIN sys.tables AS tbl ON col.object_id = tbl.object_id

INNER JOIN sys.schemas AS sch ON tbl.schema_id = sch.schema_id

WHERE sch.name = N'dbo'

AND tbl.name = @backup_table

) AS backup_col ON new_col.name = backup_col.name

WHERE new_sch.name = N'cdc'

AND new_tbl.name = @ct_table;

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

-- Step 6: Restore historical change data into the new CT table

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

IF @common_cols IS NOT NULL

BEGIN

SET @sql = N'INSERT INTO [cdc].' + QUOTENAME(@ct_table)

+ N' (' + @common_cols + N')'

+ N' SELECT ' + @common_cols

+ N' FROM [dbo].' + QUOTENAME(@backup_table) + N';';

EXEC sp_executesql @stmt = @sql;

END;

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

-- Step 7: Drop the staging table

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

SET @sql = N'DROP TABLE [dbo].' + QUOTENAME(@backup_table) + N';';

EXEC sp_executesql @stmt = @sql;

PRINT CONCAT('RECYCLED CDC capture instance [', @capture_instance, '] on [', @schema_name, '].[', @table_name, '] -- schema drift resolved');

END TRY

BEGIN CATCH

IF @cdc_disabled = 1

BEGIN

PRINT CONCAT(

'CRITICAL ERROR on [', @schema_name, '].[', @table_name, ']: ',

'The old CDC capture instance [', @capture_instance, '] was DISABLED but re-enabling FAILED. ',

'This table is NO LONGER TRACKED by CDC -- changes are NOT being captured. ',

'The backup table [dbo].', QUOTENAME(@backup_table), ' has been preserved and contains the historical change data. ',

'You must manually re-enable CDC on this table and restore the backup data. ',

'Error: ', ERROR_MESSAGE());

END

ELSE

BEGIN

PRINT CONCAT('ERROR recycling CDC on [', @schema_name, '].[', @table_name, ']: ', ERROR_MESSAGE());

END;

END CATCH;

END;

END;

FETCH NEXT FROM tables_for_cdc INTO @schema_name, @table_name, @capture_instance;

END;

CLOSE tables_for_cdc;

DEALLOCATE tables_for_cdc;

PRINT 'CDC schema drift resolution complete.';