CODEWITHSUNDEEP
sql-servert-sqlcdc
George Paoli
George Paoli

Reputation: 2595

Check if CDC is enabled on database and table in SQL Server by query

How check if CDC is enabled on specific database and table in SQL Server by running a SQL query?

Upvotes: 8

Views: 31714

Answers (1)

Thom A
Thom A

Reputation: 95588

From the documentation for sys.sp_cdc_enable_db (Transact-SQL) in the Remarks section:

sys.sp_cdc_enable_db creates the change data capture objects that have database wide scope, including meta data tables and DDL triggers. It also creates the cdc schema and cdc database user and sets the is_cdc_enabled column for the database entry in the sys.databases catalog view to 1.

select
  name,
  is_cdc_enabled
from sys.databases;

From the documentation for sys.sp_cdc_enable_table (Transact-SQL), also in the Remarks section:

sys.sp_cdc_enable_table also creates the capture and cleanup jobs for the database if the source table is the first table in the database to be enabled for change data capture and no transactional publications exist for the database. It sets the is_tracked_by_cdc column in the sys.tables catalog view to 1.

select
  name,
  is_tracked_by_cdc 
from sys.tables;

Upvotes: 15

Related Questions