--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;
Comments