SQL中的CDC 变更数据捕获
1.有时候我们需要知道cdc捕获的数据中（仅修改举例），哪些列是修改列，于是写了个存储过程，如下：
if exists
(
select *
from sys.procedures
where name = 'c_fn_cdc_parsingName'
)
drop procedure c_fn_cdc_parsingName;
go
create procedure c_fn_cdc_parsingName
(
@tbl nvarchar(1000) --cdc生成的表名，如dbo_aaa
, @update_mask varbinary(128) --_$update_mask列通过位图显示在_$operation 指定的DML 操作中更新了哪些列。如果这是一个 DELETE 或 INSERT 操作，则所有列都将被更新，因此掩码包含其中全为 1 的值。该掩码包含由位值形成的值。
)
as
begin
declare @str nvarchar(max)
, @change_table nvarchar(1000)
, @i int
, @j int;
set @str = N'';
set @change_table = N'[cdc].' + quotename(rtrim(@tbl) + N'_CT');
if exists (select * from sys.tables where name = 'temp_column')
begin
drop table temp_column;
end;
select row_number() over (order by a.column_id asc) as [rows]
, a.[name]
into temp_column
from sys.columns a
where object_id = object_id(@change_table);
select @i = count(*)
from dbo.temp_column;
set @j = 1;
while (@j < @i)
begin
declare @Name nvarchar(100)
, @num int
, @isOk bit;
select @Name = name
from temp_column
where rows = @j;
select @num = [sys].[fn_cdc_get_column_ordinal](rtrim(@tbl), @Name);
select @isOk = [sys].[fn_cdc_is_bit_set](@num, @update_mask);
if @isOk = 1
begin
set @str = @str + @Name + N',';
print @str
end;
set @j = @j + 1;
end;
select left(@str,len(@str)-1) ;
end;
合并修改列到结果集
if exists (select * from sys.procedures where name = 'p_NewDATA')
drop procedure p_NewDATA;
go
create procedure p_NewDATA
(@tbl nvarchar(1000),@operation int)
as
begin
if exists (select * from sys.tables where name = 'temp_NameTbl')
begin
drop table temp_NameTbl;
end;
declare @i int
, @j int;
select row_number() over (order by cInvCode asc) as [rows]
, cast(N'' as nvarchar(max)) as UpdateInfo
, *
into temp_NameTbl
from cdc.dbo_inventory_CT
where __$operation = @operation;
set @i =
(
select count(*) from temp_NameTbl where __$operation = @operation
);
set @j = 1;
while (@j < @i)
begin
declare @update_mask varbinary(128)
, @strName nvarchar(max);
set @strName = N'';
create table #temp
(
id int primary key identity(1, 1)
, strName nvarchar(max)
);
set @update_mask =
(
select __$update_mask from temp_NameTbl where rows = @j
);
insert into #temp
(
strName
) exec c_fn_cdc_parsingName @tbl, @update_mask;
select @strName = strName
from #temp;
update temp_NameTbl
set UpdateInfo = @strName
from dbo.temp_NameTbl
where rows = @j;
set @j = @j + 1;
drop table #temp;
end;
select *
from temp_NameTbl;
end;
