cdc.change_tables (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 nameData typeDescription
object_idintID of the change table. Is unique within a database.
versionintIdentified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For SQL Server 2012, this column always returns 0.
source_object_idintID of the source table enabled for change data capture.
capture_instancesysnameName 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_lsnbinary(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_lsnbinary(10)Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

For SQL Server 2008, this column always returns NULL.
supports_net_changesbitSupport for querying for net changes is enabled for the change table.
has_drop_pendingbitCapture process has received notification that the source table has been dropped.
role_namesysnameName of the database role used to gate access to change data.

NULL = a role is not used.
index_namesysnameName 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.

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 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_namesysnameName of the filegroup in which the change table resides.

NULL = change table is in the default filegroup of the database.
create_datedatetimeDate that the source table was enabled.
partition_switchbitIndicates 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.

sys.sp_cdc_help_change_data_capture (Transact-SQL)

Community Additions

ADD
Show: