Database : 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:

1. create procedure sp_enable_disable_cdc_all_tables(@dbname varchar(100), @enable bit)  
2. as  
3.   
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  
12.   
13. WHILE @@FETCH_STATUS = 0  
14. BEGIN  
15. if @enable = 1  
16.   
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'+''''  
20.             
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)  
25.   
26.   
27.   FETCH NEXT FROM the_cursor INTO @source_name  
28.   
29. END  
30.   
31. CLOSE the_cursor  
32. DEALLOCATE the_cursor  
33.   
34.       
35. SELECT 'Successful'  
36. END TRY  
37. BEGIN CATCH  
38. CLOSE the_cursor  
39. DEALLOCATE the_cursor  
40.   
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.