Script to Enable and use CDC in MS SQL Server

Recently at a training I was asked about CDC and while this script is old and previously published I couldn’t find it within the blog after I migrated to Azure. So re-posting. Enjoy !!

USE MADWORKS
GO

--THIS ENABLES CDC ON THE DATABASE 
sys.sp_cdc_enable_db
GO

-- CREATING A SCHEMA AND TABLE TO BE USED IN CDC 
CREATE SCHEMA example AUTHORIZATION dbo
GO

CREATE TABLE example.cdcsourcetable (
    sourceid TINYINT PRIMARY KEY CLUSTERED
    ,name VARCHAR(256)
    ,createddatetime DATETIME
    ,Updatedatetime DATETIME DEFAULT GETDATE() NOT NULL
    )
GO

SELECT *
FROM example.cdcsourcetable
GO

-- INSERTING SOME DATA INTO THE TABLE 
INSERT INTO example.cdcsourcetable (
    sourceid
    ,name
    ,createddatetime
    )
SELECT 2
    ,'Jayanth Kurup '
    ,GETDATE() - 1
GO

-- ENABLE CDC ON THE SOURCE TABLE CREATED ABOVE 
sys.sp_cdc_enable_table @source_schema = 'example'
    ,--- NAME OF THE SCHEMA TO WHICH THE SOURCE TABLE BELONGS 
    @source_name = 'cdcsourcetable'
    ,-- NAME OF THE SOURCE TABLE WHICH NEEDS TO BE MONITORED 
    @role_name = 'cdcadmin'
    ,-- A ROLE ASSIGNED TO CDC TO PERFORM ITS OPERATIONS 
    @capture_instance = 'cdc_example_Sourcetable'
    ,-- A NAME ASSIGNED TO THE CDC THAT IS BEING CREATED, THIS IS USED TO NAME THE FUNCTIONS WHICH WILL BE CREATED 
    ---TO ALLOW YOU TO QUERY THE CDC DATA 
    @supports_net_changes = 1
    ,-- IF 1 THEN DISPLAY OPTION TO GET SUM OF ALL CHANGES , IF 0 SHOWS A LIST OF ALL CHANGES 
    @index_name = 'PK_cdcsourcetable'
    ,-- THE PRIMARY KEY INDEX OF THE TABLE , USED TO IDENTIFY UNIQUE ROWS IN THE TABLE I.E ROW VERSIONING. 
    @captured_column_list = 'sourceid,name,createddatetime' -- COMMA DELIMITED SET OF COLUMNS WITHIN THE TABLE THAT NEED TO BE MONITORED 
GO

-- INSERT THE CHANGE DATA 
INSERT INTO example.cdcsourcetable (
    sourceid
    ,name
    ,createddatetime
    )
SELECT 2
    ,'Jayanth Kurup '
    ,GETDATE() - 1
GO

-- QUERY CDC FOR CHANGES /*SINCE CDC WORKS BY QUERYING THE TRANSCATION LOG FOR CHANGES AND DML THE LSN IS USED TO KEEP TRACK OF THE ORDER FOR THE CHANGES. THE FUNCTION fn_cdc_map_time_to_lsn ALLOWS YOU TO TRACK AN LSN AS PART OF THE TIMESTAMP WHEN THE TRANSACTION OCCURRED. THIS IS USED EVEN WHEN PERFORMING A POINT IN TIME RECOVERY. THE FUNCTION cdc.fn_cdc_get_all_changes_cdc_example_cdcsourcetable IS USED TO QUERY THE CDC AND IDENTIFY THE CHANGED RECORDS.*/ 
DECLARE @from_lsn BINARY (10)
DECLARE @to_lsn BINARY (10)

SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', GETDATE() - 1);

SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', GETDATE());

-- Return the changes occurring within the query window. 
SELECT *
    ,'Operation' = CASE 
        WHEN __$operation = 1
            THEN 'DELETE'
        WHEN __$operation = 2
            THEN 'INSERT'
        WHEN __$operation = 3
            THEN 'UPDATE BEFORE IMAGE'
        WHEN __$operation = 4
            THEN 'UPDATE AFTER IMAGE'
        ELSE 'ERROR'
        END
FROM cdc.fn_cdc_get_all_changes_cdc_example_cdcsourcetable(@from_lsn, @to_lsn, 'all');GO --RESULT SET

Please Consider Subscribing

Subscribe