How To Check If CDC Is Enabled On A Table In SQL Server

As a developer on one of my projects, checking whether the CDC is enabled on a table in the SQL server was a daily task. I have identified a few simple approaches to do this. In this article, I will take you through all these approaches.

Approach-1: Using sys.tables

You can easily use the sys.tables to check identity column value in the SQL server.

Syntax

SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE name = 'TableName';

Example

The query below will check if the CDC is enabled on the Product table.

SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE name = 'Product';

After executing the above script, I got the expected output, which is shown in the screenshot below.

Note: When is_tracked_by_cdc is 1, then the CDC is enabled; if it is 0, it is disabled.

Check out How To Enable CDC On A Table In SQL Server

Approach-2: Using sys.databases

You can use sys.databases to verify if the CDC is enabled at the database level.

Syntax

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'DatabaseName';

Example

The below query will check if the CDC is enabled at the Test database level.

SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'Test';

After executing the above script, I got the expected output, shown in the screenshot below.

Check out How To Check Table Description In SQL Server

Approach-3: Using cdc.TableName_CT

Execute the query below to check if the CDC is enabled on the product table.

IF OBJECT_ID('cdc.Product_CT') IS NOT NULL
    PRINT 'CDC is enabled for this table'
ELSE
    PRINT 'CDC is not enabled for this table';

After executing the above query, I got the expected output as shown below.

Conclusion

For SQL developers, checking if CDC is enabled on a table in an SQL Server is crucial. You can quickly check the CDC status using any of the methods explained in this article.

You may also like following the articles below.

• How To Rename The Table Name In SQL Server
• How To Create A Table Variable In SQL Server
• How to create index on temp table in SQL Server
• How To Check User Permissions On Table In SQL Server