cdc.change_tables (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns one row for each change table in the database. A change table is created when change data capture is enabled on a source table. We recommend that you don't query the system tables directly. Instead, execute the sys.sp_cdc_help_change_data_capture stored procedure.

Column name Data type Description
object_id int ID of the change table. Is unique within a database.
version int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For SQL Server 2012 (11.x), this column always returns 0.
source_object_id int ID of the source table enabled for change data capture.
capture_instance sysname Name of the capture instance used to name instance-specific tracking objects. By default, the name is derived from the source schema name plus the source table name in the format schemaname_sourcename.
start_lsn binary(10) Log sequence number (LSN) representing the low endpoint when querying for change data in the change table.

NULL = the low endpoint hasn't been established.
end_lsn binary(10) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For SQL Server 2008 (10.0.x), this column always returns NULL.
supports_net_changes bit Support for querying for net changes is enabled for the change table.
has_drop_pending bit Capture process has received notification that the source table has been dropped.
role_name sysname Name of the database role used to gate access to change data.

NULL = a role isn't used.
index_name sysname Name of the index used to uniquely identify rows in the source table. index_name is either the name of the primary key index of the source table, or the name of a unique index specified when change data capture was enabled on the source table.

NULL = source table didn't have a primary key when change data capture was enabled and a unique index wasn't specified when change data capture was enabled.

Note: If change data capture is enabled on a table where a primary key exists, the change data capture feature uses the index regardless of whether net changes are enabled or not. After change data capture is enabled, no modification is allowed on the primary key. If there's no primary key on the table, you can still enable change data capture but only with net changes set to false. After change data capture is enabled, you can then create a primary key. You can also modify the primary key because change data capture doesn't use the primary key.
filegroup_name sysname Name of the filegroup in which the change table resides.

NULL = change table is in the default filegroup of the database.
create_date datetime Date that the source table was enabled.
partition_switch bit Indicates whether the SWITCH PARTITION command of ALTER TABLE can be executed against a table that is enabled for change data capture. 0 indicates that partition switching is blocked. Non-partitioned tables always return 1.

See Also

sys.sp_cdc_help_change_data_capture (Transact-SQL)