如何在MS SQL中跟踪DDL变更并创建存储过程记录列修改

SQL Server系统视图sys.columns里的create_date和modify_date都是表级时间戳，并非列单独的创建/修改时间，因此无法通过直接查询系统视图获取列的变更历史。要实现列级DDL操作（新增、修改、删除列）及表的创建/删除跟踪，必须通过DDL触发器在变更发生时实时捕获并记录信息。

步骤1：创建变更日志表 #

先建立一张专门存储DDL变更细节的表：

CREATE TABLE DDLChangeLog (
    ChangeID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100),
    DatabaseName NVARCHAR(128),
    SchemaName NVARCHAR(128),
    ObjectName NVARCHAR(128),
    ChangeDetails XML,
    ChangeTime DATETIME DEFAULT GETDATE(),
    ExecutedBy NVARCHAR(128) DEFAULT SUSER_SNAME()
);

步骤2：创建DDL触发器 #

创建针对CREATE_TABLE、ALTER_TABLE、DROP_TABLE事件的触发器，解析事件的XML数据并插入日志表：

CREATE TRIGGER TrackDDLChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();

    INSERT INTO DDLChangeLog (
        EventType,
        DatabaseName,
        SchemaName,
        ObjectName,
        ChangeDetails
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'),
        @EventData
END;

步骤3：解析变更细节（可选） #

如果需要把XML中的列变更信息提取为更易读的格式，可以在查询日志表时解析XML节点：

SELECT
    ChangeID,
    EventType,
    SchemaName + '.' + ObjectName AS ObjectFullName,
    -- 提取ALTER_TABLE中的列操作类型（新增/修改/删除）
    CASE 
        WHEN EventType = 'ALTER_TABLE' THEN
            @EventData.value('(/EVENT_INSTANCE/AlterTableActionList/AlterAction/Action)[1]', 'NVARCHAR(50)')
        ELSE NULL
    END AS ColumnAction,
    -- 提取涉及的列名
    CASE 
        WHEN EventType = 'ALTER_TABLE' THEN
            @EventData.value('(/EVENT_INSTANCE/AlterTableActionList/AlterAction/ColumnName)[1]', 'NVARCHAR(128)')
        ELSE NULL
    END AS ColumnName,
    ChangeTime,
    ExecutedBy
FROM DDLChangeLog
WHERE EventType IN ('CREATE_TABLE', 'ALTER_TABLE', 'DROP_TABLE');

关键说明 #

• 触发器会在执行CREATE TABLE、ALTER TABLE（含新增列、修改列属性、删除列）、DROP TABLE时自动触发，将事件完整的XML数据存入日志表，XML中包含变更的所有细节（如列的数据类型、约束、操作类型等）。
• 若只需跟踪列相关的变更，可在触发器中增加XML节点判断，过滤掉ALTER TABLE的其他操作（如修改约束、重命名表等）。

内容的提问来源于stack exchange，提问作者Prabakaran