Change Data Capture

--List of tables with CDC enabled

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

order by tb.name;

---

--Disable Change Data Capture at Database Level

EXEC sys.sp_cdc_disable_db

GO

--Disable Change Data Capture for one table

declare @capture_instance sysname;

select @capture_instance = capture_instance

from cdc.change_tables where source_object_id = object_id('Table_Name');

EXEC sys.sp_cdc_disable_table

@source_schema = N'dbo',

@source_name = N'Table_Name',

@capture_instance = @capture_instance;

GO