CDC 常用SP:

EXEC sys.sp_cdc_enable_db; EXEC sys.sp_cdc_enable_table @source_schema= 'dbo', @source_name = 'Patient',@role_name = null ; --specify database's role to view cdc information, use null to allow all EXEC sys.sp_cdc_help_change_data_capture; --view the table and cdc_instance EXEC sys.sp_cdc_disable_db; EXEC Sys.sp_cdc_disable_table @source_schema= 'dbo', @source_name = 'Patient',@capture_instance='dbo_Patient' --close the table cdc

查询CDC 的结果:

select sys.fn_cdc_map_lsn_to_time(__$start_lsn),* from [cdc].[dbo_Patient_CT]

cdc.captured_columns

为在捕获实例中跟踪的每一列返回一行

sys.sp_cdc_get_source_columns

cdc.change_tables

为数据库中的每个更改表返回一行

sys.sp_cdc_help_change_data_capture

cdc.ddl_history

针对启用了变更数据捕获的表所做的每一数据定义语言 (DDL) 更改返回一行

sys.sp_cdc_get_ddl_history

cdc.lsn_time_mapping

为每个在更改表中存在行的事务返回一行

sys.fn_cdc_map_lsn_to_time (Transact-SQL) ,sys.fn_cdc_map_time_to_lsn (Transact-SQL)

cdc.index_column

为与更改表关联的每一索引列返回一行

sys.sp_cdc_help_change_data_capture

msdb.dbo.cdc_jobs

存储用于捕获和清除作业的变更数据捕获配置参数

NA

cdc.<capture_instance>_CT

对源表启用变更数据捕获时创建的更改表。 该表为对源表执行的每个插入和删除操作返回一行，为对源表执行的每个更新操作返回两行.capture_instance格式=SchameName_TableName

cdc.fn_cdc_get_all_changes_<capture_instance> ,

cdc.fn_cdc_get_net_changes_<capture_instance>

__$start_lsn

binary(10)

更改提交的LSN。在同一事务中提交的更改将共享同一个提交 LSN 值。

__$seqval

binary(10)

一个事务内可能有多个更改发生，这个值用于对它们进行排序。

__$operation

int

更改操作的类型：

1 = 删除

2 = 插入

3 = 更新（捕获的列值是执行更新操作前的值）。

4 = 更新（捕获的列值是执行更新操作后的值）。

__$update_mask

varbinary(128)

位掩码，源表中被CDC跟踪的每一列对应一个位。如果 __$operation = 1 或 2，该值将所有已定义的位设置为 1。如果__$operation = 3 或 4，则只有那些对应已更改列的位设置为 1。

sys.sp_cdc_help_jobs:

job_iduniqueidentifierThe ID of the job.job_typenvarchar(20)The type of job.maxtransintThe maximum number of transactions to process in each scan cycle. maxtrans is valid only for capture jobs.maxscansintThe maximum number of scan cycles to execute in order to extract all rows from the log. maxscans is valid only for capture jobs.continuousbitA flag indicating whether the capture job is to run continuously (1), or run in one-time mode (0). For more information, seesys.sp_cdc_add_job (Transact-SQL). continuous is valid only for capture jobs.pollingintervalbigintThe number of seconds between log scan cycles. pollinginterval is valid only for capture jobs.retentionbigintThe number of minutes that change rows are to be retained in change tables. retention is valid only for cleanup jobs.thresholdbigintThe maximum number of delete entries that can be deleted using a single statement on cleanup.