SQLSERVER批量关闭cdc.md

• 关闭

USE xxxxxxxxxxxxxxxxxx
GO
EXECUTE sys.sp_cdc_disable_db;
GO

• 批量关闭所有的cdc表 #游标

DECLARE @name NVARCHAR(50)    --声明变量，需要读取的数据
DECLARE cur CURSOR                --去掉STATIC关键字即可
FOR
-- 根据实际情况排除
     SELECT name FROM sys.tables where is_tracked_by_cdc=1  ORDER BY name asc
OPEN cur                            --打开游标
FETCH NEXT FROM cur INTO  @name     --取数据
WHILE ( @@fetch_status = 0 )        --判断是否还有数据
    BEGIN        
    	PRINT @name 
        EXEC sys.sp_cdc_disable_table
        @source_schema = 'dbo', -- source_schemaac
        @source_name =  @name, -- table_name					        
        @capture_instance = 'all'
        FETCH NEXT FROM cur INTO  @name  
    END
CLOSE cur                               --关闭游标
DEALLOCATE cur

• 查看开启cdc的库

select * from sys.databases where is_cdc_enabled = 1;