七、SqlServer数据库，批量开启表cdc

启用/禁用数据库cdc

# 查看已启用cdc的数据库
SELECT name,is_cdc_enabled FROM sys.databases WHERE is_cdc_enabled = 1;

# 启用数据库cdc
EXECUTE sys.sp_cdc_enable_db;

#禁用数据库cdc
EXECUTE sys.sp_cdc_disable_db;

启用/禁用表cdc

#查看已开启cdc的表
SELECT name,is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;

#启用表cdc
EXEC sys.sp_cdc_enable_table
    @source_schema = 'schema_name',
	@source_name   = 'table_name',
	@role_name     = NULL

# 不需要控制权限，则role_name为NULL

#禁用表cdc
EXEC sys.sp_cdc_disable_table
	@source_schema = 'schema_name',
	@source_name   =  'table_name',
	@capture_instance = 'cdc.change_tables中capture_instance字段'

批量开启表cdc，示例

begin
declare @temp varchar(100)
-- 申明游标为表名
declare tb_cursor cursor
    for (select name from sys.tables WHERE is_tracked_by_cdc=0 and schema_id=1)
-- 打开游标
open tb_cursor
-- 开始循环游标变量
fetch next from tb_cursor into @temp
-- 返回fetch语句执行的最后游标的状态
while @@fetch_status=0
	begin
	EXEC sys.sp_cdc_enable_table
	@source_schema = 'dbo',
	@source_name   = @temp,
	@role_name     = NULL
	-- 转到下一个游标
	fetch next from tb_cursor into @temp
	end
-- 关闭游标
close tb_cursor
-- 释放游标
deallocate tb_cursor
end ;