CDC (Change Data Capture) 範例

EXEC sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases
exec sp_cdc_enable_table 'dbo','tblA',@role_name='sa'
select name, type, type_desc, is_tracked_by_cdc from sys.tables
select * from tbla
insert into tblA values ('6','aga')
select * from cdc.dbo_tblA_CT --__$operation欄位1表示DELETE 2表示INSERT 3表示UPDATE之前 4表示UPDATE之後
Declare @begin_LSN binary(10),@End_LSN binary(10)
select @begin_LSN=sys.fn_cdc_get_min_lsn('dbo_tblA')
select @End_LSN=sys.fn_cdc_get_max_lsn()
select @begin_LSN,@End_LSN
select * from cdc.fn_cdc_get_net_changes_dbo_tblA(@begin_LSN,@End_LSN,'ALL');
select * from cdc.fn_cdc_get_all_changes_dbo_tblA(@begin_LSN,@End_LSN,'ALL');
select * from cdc.lsn_time_mapping --用來查看LSN的時間
exec test.sys.sp_cdc_disable_table @source_schema='dbo',@source_name='tblA',@capture_instance='dbo_tblA'
EXEC sp_cdc_disable_db