Disable Change Data Capture for multiple tables

--disable cdc master

--Table Contains list of all the tables where cdc needs to be disabled - [dba].dbo.replication_cdc_table

--execute the output of this script to disable the cdc

/****** Script for SelectTopNRows command from SSMS ******/

SET ANSI_NULLS ON;

SET ARITHABORT ON;

SET QUOTED_IDENTIFIER ON;

SET NOCOUNT ON;

Declare @dbname varchar(max)

DECLARE @command nvarchar(4000);

DECLARE db_names CURSOR FOR select [table_name] from [dba].dbo.replication_cdc_table;

open db_names;

fetch next from db_names into @dbname;

while @@FETCH_STATUS = 0

Begin

Set @command = ' declare @capture_instance sysname;

select @capture_instance = capture_instance

from cdc.change_tables where source_object_id = object_id('''+@dbname+''');

EXEC sys.sp_cdc_disable_table

@source_schema = N''dbo'',

@source_name = N'''+@dbname+''',

@capture_instance = @capture_instance;

GO'

fetch next from db_names into @dbname

--execute (@command);

print @command

End

close db_names;

deallocate db_names;