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.
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!