Share via


cdc.change_tables (Transact-SQL)

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 do not 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 2008, 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 has not been established.

end_lsn

binary(10)

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For SQL Server 2008, 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 is not 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 did not have a primary key when change data capture was enabled and a unique index was not specified when change data capture was enabled.

NoteNote
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 is enabled or not. After change data capture is enabled, no modification is allowed on the primary key. If there is 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 does not 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.