Recently at a training I was asked about CDC and while this script is old and previously published i couldnt 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