Export (0) Print
Expand All

sys.sp_cdc_get_ddl_history (Transact-SQL)

Returns the data definition language (DDL) change history associated with the specified capture instance since change data capture was enabled for that capture instance. Change data capture is not available in every edition of Microsoft SQL Server. For a list of features that are supported by the editions of SQL Server, see Features Supported by the Editions of SQL Server 2012.

Topic link icon Transact-SQL Syntax Conventions

sys.sp_cdc_get_ddl_history [ @capture_instance = ] 'capture_instance'

[ @capture_instance = ] 'capture_instance'

Is the name of the capture instance associated with a source table. capture_instance is sysname and cannot be NULL.

0 (success) or 1 (failure)

Column name

Data type

Description

source_schema

sysname

Name of the source table schema.

source_table

sysname

Name of the source table.

capture_instance

sysname

Name of the capture instance.

required_column_update

bit

Indicates the DDL change required a column in the change table to be altered to reflect a data type change made to the source column.

ddl_command

nvarchar(max)

The DDL statement applied to the source table.

ddl_lsn

binary(10)

Log sequence number (LSN) associated with the DDL change.

ddl_time

datetime

Time associated with the DDL change.

DDL modifications to the source table that change the source table column structure, such as adding or dropping a column, or changing the data type of an existing column, are maintained in the cdc.ddl_history table. These changes can be reported by using this stored procedure. Entries in cdc.ddl_history are made at the time the capture process reads the DDL transaction in the log.

Requires membership in the db_owner fixed database role to return rows for all capture instances in the database. For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role.

The following example adds a column to the source table HumanResources.Employee and then runs the sys.sp_cdc_get_ddl_history stored procedure to report the DDL changes that apply to the source table associated with the capture instance HumanResources_Employee.

USE AdventureWorks2012;
GO
ALTER TABLE HumanResources.Employee
ADD Test_Column int NULL;
GO
-- Pause 10 seconds to allow the event to be logged. 
WAITFOR DELAY '00:00:10';
GO 
EXECUTE sys.sp_cdc_get_ddl_history 
    @capture_instance = 'HumanResources_Employee';
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft