SQLSERVER批量关闭cdc

2023-02-03

mssql   cdc   hidden   

USE xxxxxxxxxxxxxxxxxx
GO
EXECUTE sys.sp_cdc_disable_db;
GO

-
- - 批量关闭所有的cdc表 #游标
- ```sql
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的库
```sql
select * from sys.databases where is_cdc_enabled = 1;