How Can I Check Whether I Have Enabled Change Data Capture (CDC) on Databases and Tables?

Question

How can I check whether I have enabled change data capture (CDC) on individual databases and tables?

Environment

• Generic SQL Server
• Amazon RDS for SQL Server
• Azure SQL Database
• Azure SQL Managed Instance
• Google Cloud SQL for SQL Server

Answer

You can check whether you have enabled CDC by querying the sys.databases and sys.tables SQL Server catalog views.

To check whether you have enabled CDC on a database, use the following query:

SELECT name, is_cdc_enabled FROM sys.databases;

CDC is enabled if the value of the is_cdc_enabled column is 1. If the query returns any other value, CDC is not enabled.

To check whether you have enabled CDC on a table, use the following query:

SELECT
  name AS table_name,
  OBJECT_SCHEMA_NAME(object_id) AS table_schema,
  is_tracked_by_cdc
FROM sys.tables
-- OPTIONAL: to filter for a specific schema/table
-- WHERE
-- OBJECT_SCHEMA_NAME(object_id) = '<schema_name>'
-- AND name = '<table_name>';

CDC is enabled if the value of is_tracked_by_cdc column is 1. If the query returns any other value, CDC is not enabled.