Today, I had to remove cdc from all tables on a database to fix a particular issue. Here is the script to disable the cdc at table level.

Script

Select 'EXEC sys.sp_cdc_disable_table
 @source_schema = ''' + OBJECT_SCHEMA_NAME (source_object_id) +''',
 @source_name = ''' + object_name(source_object_id)+ ''',
 @capture_instance = '''+ capture_instance +''';',* From cdc.change_tables

Please note, the above script is not actually disabling the cdc on tables, but generates the statements which can be used to disable the CDC on tables. This is just to make sure we review before we apply the disable commands.

I would also suggest to execute the below to disable the Change Data Capture on the database as well.

use dbname
EXEC sys.sp_cdc_disable_db  

If you enjoyed this blog post, feel free to share it with your friends!