Disable cdc for all tables in SQL Server

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s