Works for SQL Server starting with 2008.
Enable change data capture for a database:
EXEC sys.sp_cdc_enable_db
GO
Check if it is enabled:
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
WHERE is_cdc_enabled = 1
GO
Enable change data capture for a table:
EXEC sys.sp_cdc_enable_table
@source_schema = N‘dbo’,
@source_name= N‘MyTable’,
@role_name= NULL,
@filegroup_name = N‘CDC’,
@supports_net_changes = 0
GO
Check if table is tracked:
SELECT [name], is_tracked_by_cdc
FROM sys.tables
WHERE is_tracked_by_cdc=1
GO
Select changes:
select * from [cdc].SchemaName_SourceTableName_CT
Cleanup tracked changes:
EXECUTE sys.sp_cdc_change_job
@job_type = N‘cleanup’,
@retention = 2880;
Leave a Reply