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.