Change data capture (CDC) is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Here is the complete Example of CDC
Here is the complete Example of CDC
Create Database CDCDB
Go
USE CDCDB
Go
EXEC sp_cdc_enable_db
SELECT * FROM sys.databases
Create table Employee
(EMPNO int,
empname varchar(50))
Alter table Employee
Add EMPADD varchar(50)
exec sys.sp_cdc
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'Employee'
, @role_name = N'cdc_Admin';
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo'
, @source_name = N'Employee'
, @capture_instance='dbo_Employee';
Go
SELECT * FROM cdc.ddl_history
--TRUNCATE TABLE EMPLOYEE
SELECT * FROM EMPLOYEE
Insert into Employee values (1,'MYBASIC',null)
Insert into Employee values (2,'KNOWLEDGE',null)
DELETE FROM Employee WHERE empno=2
UPDATE Employee SET EMPNAME='MYBASIC KNOWLEDGE' WHERE empno=2
SELECT * FROM cdc.dbo_employee_CT
SELECT * FROM
cdc.fn_cdc_get_all_changes_dbo_employee(0x00000025000000960003,0x00000025000000D70005,'all')
exec sys.sp_cdc_help_change_data_capture
SELECT sys.fn_cdc_map_time_to_lsn ('smallest greater than or
equal',Getdate()-1)
