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:
  1. create procedure sp_enable_disable_cdc_all_tables(@dbname varchar(100), @enable bit)
  2. as
  4. BEGIN TRY
  5. DECLARE @source_name varchar(400);
  6. declare @sql varchar(1000)
  7. DECLARE the_cursor CURSOR FAST_FORWARD FOR
  8. SELECT table_name
  9. FROM INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=@dbname and table_schema='dbo' and table_name != 'systranschemas'
  10. OPEN the_cursor
  11. FETCH NEXT FROM the_cursor INTO @source_name
  13. WHILE @@FETCH_STATUS = 0
  14. BEGIN
  15. if @enable = 1
  17. set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_enable_table
  18. @source_schema = N''dbo'',@source_name = '+@source_name+'
  19. , @role_name = N'''+'dbo'+''''
  21. else
  22. set @sql =' Use '+ @dbname+ ';EXEC sys.sp_cdc_disable_table
  23. @source_schema = N''dbo'',@source_name = '+@source_name+', @capture_instance =''all'''
  24. exec(@sql)
  27. FETCH NEXT FROM the_cursor INTO @source_name
  29. END
  31. CLOSE the_cursor
  32. DEALLOCATE the_cursor
  35. SELECT 'Successful'
  36. END TRY
  37. BEGIN CATCH
  38. CLOSE the_cursor
  39. DEALLOCATE the_cursor
  41. SELECT
  42. ERROR_NUMBER() AS ErrorNumber
  43. ,ERROR_MESSAGE() AS ErrorMessage;
  44. 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.