SQL Server Query for Enabling Change Data Capture On All Tables

Change Data Capture (CDC) isan auditing feature provided by SQL Server 2008 and above and used to audit/track all changes done on a table such as inserts, updates, and deletes. We can enable CDC on the database using exec sys.sp_cdc_enable_db then we need to enable the same on each table manually using sys.sp_cdc_enable_table with table name as parameter. Instead of that, we can use the following SP to enable/disable CDC at the table level for all tables in a database:

create procedure sp_enable_disable_cdc_all_tables(@dbname varchar(100), @enable bit)
as

BEGIN TRY
DECLARE @source_name varchar(400);
declare @sql varchar(1000)
DECLARE the_cursor CURSOR FAST_FORWARD FOR
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=@dbname and table_schema='dbo' and table_name != 'systranschemas'
OPEN the_cursor
FETCH NEXT FROM the_cursor INTO @source_name

WHILE @@FETCH_STATUS = 0
BEGIN
if @enable = 1

set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_enable_table
            @source_schema = N''dbo'',@source_name = '+@source_name+'
          , @role_name = N'''+'dbo'+''''

else
set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_disable_table
            @source_schema = N''dbo'',@source_name = '+@source_name+',  @capture_instance =''all'''
exec(@sql)


  FETCH NEXT FROM the_cursor INTO @source_name

END

CLOSE the_cursor
DEALLOCATE the_cursor


SELECT 'Successful'
END TRY
BEGIN CATCH
CLOSE the_cursor
DEALLOCATE the_cursor

    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

This SP takes db name and flags enable/disable as inputs and loops through each table name from INFORMATION_SCHEMA.TABLES using the cursor and calling dynamic SQL with command sys.sp_cdc_enable_table in it. 

 

 We can re-usethe same SP for doing any operation on every table in a database.