Change data
capture provides information about DML changes on a table and a database.
Easy Steps to
run TTthe ChangeDataCapture (CDC)
USE [master]
Go
Create Database Change_Data_Capture
Go
Use
Change_Data_Capture
Go
EXEC sys.sp_cdc_enable_db
Go
Go
Create table Test
(
TestKey Int Identity(1,1) Primary Key NOT NULL,
TestName varchar(25)
)
Use
Change_Data_Capture
Go
EXEC sp_cdc_enable_table 'dbo', 'Test', @role_name = NULL, @supports_net_changes =1
Go
----Job 'cdc.Change_Data_Capture_capture' started successfully.
--- Job 'cdc.Change_Data_Capture_cleanup' started successfully.
Use
Change_Data_Capture
Go
Select name, is_tracked_by_cdc from
sys.tables
Go
insert into Test
select 'R1'
union select 'R2'
union select 'R3'
union select 'R4'
Go
Select * from cdc.dbo_Test_CT
Go
Update Test
Set TestName = 'R1MODIFIED'
Where TestKey = 1
Go
Delete From Test
Where TestKey = 2
Go
Select * from cdc.dbo_Test_CT
Go
delete from Test
Go
Select * from cdc.dbo_Test_CT
Go
truncate table Test
-- Cannot truncate table 'Test1' because it is published for replication
or enabled for Change Data Capture.
drop table Test
Go
/*
_$operations = 1 Means row was Deleted
_$operations = 2 Means row was Inserted
_$operations = 3 Means value of row before update
_$operations = 4 Means value of row after update
*/
Go
use master
Go
Drop Database Change_Data_Capture
Go
SQL Server Agent needs to be Started before you run this program.