Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.

Step 1 – Creation of a database

The database name is MyDataBase

USE [master]

GO

/*** Object: Database [MyDataBase] ***/

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')

DROP DATABASE [MyDataBase]

GO

USE [master]

GO

/*** Object: Database [MyDataBase] ***/

CREATE DATABASE [MyDataBase]

GO

Step 2 - Creation of a table in MyDataBase database

USE [MyDataBase]

GO

/*** Object: Table [dbo].[MyTable] ***/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')

AND type in (N'U'))

DROP TABLE [dbo].[MyTable]

GO

USE [MyDataBase]

GO

CREATE TABLE [dbo].[MyTable](

[ID] [int] NOT NULL,

[Name] [varchar](100) NULL,

CONSTRAINT [MyTable_PK] PRIMARY KEY

GO

Step 3 - Enabling Change Data Capture feature.

The Transact SQL command enables the Change Data Capture feature.

After enabling the Change Data Capture, a schema along with objects is created.

USE [MyDataBase]

GO

EXEC sys.sp_cdc_enable_db_change_data_capture

GO

Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:

cdc.captured_columns

cdc.change_tables

cdc.ddl_history

cdc.index_columns

cdc.lsn_time_mapping