How to Check If CDC Is Enabled on a Table in SQL Server

by

In this article, I will walk you through the exact methods I use to verify CDC status on a table in SQL Server. I’ll cover everything from simple T-SQL queries to checking system stored procedures, ensuring you have the full picture.

How to Check If CDC Is Enabled on a Table in SQL Server

Prerequisites

Before we dive into the queries, let’s set the stage.

  • Minimum Requirement: Usually SELECT permissions on the system views and the specific CDC tables.
  • Best Practice: Membership in the db_owner fixed database role or the sysadmin server role ensures you can see everything, including the SQL Agent jobs that drive CDC.

Note: CDC is available in SQL Server 2016 Service Pack 1 and later for Standard Edition, and all versions of Enterprise Edition.

Method 1: The Quickest Way – Using sys.tables (T-SQL)

SQL Server exposes a specific column in the sys.tables system view called is_tracked_by_cdc. This is the most lightweight way to check status without digging into complex CDC configurations.

You can run this query against your specific database:

SQL

USE YourDatabaseName;
GO

SELECT 
    name AS TableName,
    is_tracked_by_cdc,
    CASE 
        WHEN is_tracked_by_cdc = 1 THEN 'Enabled'
        ELSE 'Disabled'
    END AS Status
FROM sys.tables
WHERE name = 'Orders'; -- Replace with your table name

What the results mean:

  • 1 (Enabled): The table is actively tracked by CDC.
  • 0 (Disabled): The table is not currently tracked.

If I want to audit the entire database to see all tables where CDC is turned on, I simply remove the WHERE clause:

After executing the query above, I received the expected output, as shown in the screenshot below.

How to check if CDC is enabled on a table in SQL server

SQL

SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName
FROM sys.tables
WHERE is_tracked_by_cdc = 1;

Method 2: The Detailed Way – Using cdc.change_tables

While sys.tables is great for a boolean check, it doesn’t tell you how the capture is configured. When I need to know the “capture instance” name (which is crucial for querying the changes later using functions like cdc.fn_cdc_get_all_changes_...), I query cdc.change_tables.

This system table exists specifically to track CDC metadata.

SQL

USE YourDatabaseName;
GO

SELECT 
    source_schema, 
    source_table, 
    capture_instance, 
    start_lsn, 
    capture_min_lsn
FROM cdc.change_tables
WHERE source_table = 'Orders'; -- Replace with your table name

Why I use this method:

If this query returns no rows, CDC is not enabled on the table, even if you thought it was. If it returns a row, you get the capture_instance name. This is vital because if you have enabled CDC on the same table multiple times (with different capture instances), you will see multiple rows here.

Method 3: The Deep Dive – Using sys.sp_cdc_help_change_data_capture

I use the built-in system stored procedure sys.sp_cdc_help_change_data_capture. This provides the most comprehensive view, including which specific columns are being captured and the filegroup being used.

SQL

USE YourDatabaseName;
GO

EXEC sys.sp_cdc_help_change_data_capture 
    @source_schema = 'Sales', 
    @source_name = 'Orders';

Key Information returned:

  • captured_column_list: Shows exactly which columns are being tracked. If you added a new column to the main table after enabling CDC, it won’t show up here (and won’t be tracked) until you re-enable CDC.
  • role_name: Tells you if there is a gating role restricting access to the change data.
  • index_name: The index used to identify rows (usually the Primary Key).

Method 4: The Visual Check (SSMS GUI)

Sometimes I just want to click through the interface rather than writing code, specifically if I’m already exploring the Object Explorer in SQL Server Management Studio (SSMS).

  1. Open SQL Server Management Studio.
  2. Expand your Database.
  3. Expand Tables.
  4. Look for System Tables.
  5. Expand System Tables -> cdc.

If you see tables named like cdc.dbo_YourTable_CT, that is a strong visual indicator that CDC is active. The _CT stands for “Change Table.”

However, I generally advise against relying solely on this. Just because the _CT table exists doesn’t strictly mean the capture job is running perfectly, but it confirms the feature was enabled on the table.

Critical Distinction: Database vs. Table Level

It is important to remember that CDC works hierarchically. You cannot enable it on a table if it isn’t enabled on the database first.

If you are troubleshooting why your table command is failing, check the database level first:

SQL

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

If this returns 0, You must enable it at the database level before you can even think about the tables.

LevelSystem ViewColumn to Check
Databasesys.databasesis_cdc_enabled
Tablesys.tablesis_tracked_by_cdc

Troubleshooting: “It says Enabled, but I see no data!”

I have faced this scenario many times. I run the check from Method 1, it says Enabled, but when I query the change table, it’s empty despite data changing in the source.

When this happens, the issue is rarely the enablement status; it is almost always the Capture Job.

CDC relies on the SQL Server Agent to run a specific job (usually named cdc.YourDBName_capture). If the Agent is down, or this job is stopped, the status will say “Enabled,” but no data will be recorded.

My checklist for this scenario:

  1. Is the SQL Server Agent service running?
  2. Is the cdc.[DatabaseName]_capture job running?
  3. Are there errors in sys.dm_cdc_errors?

Summary

To verify if CDC is enabled on a table, I recommend starting with Method 1 (sys.tables) for a quick check. If you need operational details like column lists or capture instances, move to Method 3 (sys.sp_cdc_help_change_data_capture).

You may also like the following articles: