CT and CDC Info

Checking CDC

SELECT name

FROM sys.tables where is_tracked_by_cdc=1

SELECT s.name AS Schema_Name, tb.name AS Table_Name

, tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc

FROM sys.tables tb

INNER JOIN sys.schemas s on s.schema_id = tb.schema_id

WHERE tb.is_tracked_by_cdc = 1

and s.name+'.'+tb.name in ('dbo.areas','dbo.ComponentTags','dbo.Images','dbo.Productlines',

'focalpoint.RadioFrequencyCards','taxman.Chipsets','Masterdata.Components','Masterdata.SoftwareFormats',

'Qswat.Areas','Qswat.Products') order by 1

Enabling CDC on DB :

EXEC sys.sp_cdc_enable_db

Enabling CDC on Table:

EXEC sys.sp_cdc_enable_table 

@source_schema = N'dbo', 

@source_name   = N'Test',

@role_name     = NULL

Disable CDC

EXEC sys.sp_cdc_disable_table 

@source_schema = N'dbo', 

@source_name   = N'Test',

@capture_instance  = N'dbo_Test',

Checking Change Tracking enabled or not

SELECT

S.NAME+'.'+T.NAME as TableName

FROM

SYS.TABLES T INNER JOIN SYS.SCHEMAS S

ON T.schema_id = S.schema_id

INNER JOIN SYS.CHANGE_TRACKING_TABLES CT

ON T.object_id = CT.object_id

Enabling CT on database

ALTER DATABASE AdventureWorks2012 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Enable CT

Alter table dbo.test

Enable change_tracking

WITH (TRACK_COLUMNS_UPDATED = OFF);

Disable CT:

Alter table dbo.test

disable change_tracking;