SQL Server中如何获取列的创建时间及变更信息？

SQL Server默认不会在系统视图中存储列的历史变更记录（如创建时间、修改轨迹），sys.columns等视图仅保留列的当前状态。要实现列的创建/变更追踪，需通过主动监控手段，以下是可行方案：

一、改进版DDL触发器（自定义追踪） #

你之前的触发器存在无法处理多列变更、动作区分模糊、旧类型读取错误等问题，以下是修正后的实现：

1. 创建变更记录表 #

DROP TABLE IF EXISTS ColumnChanges;
CREATE TABLE ColumnChanges (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DatabaseName NVARCHAR(128),
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    ActionType NVARCHAR(20), -- 区分'ADD'新增 / 'ALTER'修改
    OldDataType NVARCHAR(100),
    NewDataType NVARCHAR(100),
    ChangeDateTime DATETIME DEFAULT GETDATE()
);

2. 创建数据库级ALTER_TABLE触发器 #

CREATE OR ALTER TRIGGER trg_ColumnChange_AfterAlter
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();
    DECLARE @DatabaseName NVARCHAR(128) = @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)');
    DECLARE @SchemaName NVARCHAR(128) = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)');
    DECLARE @TableName NVARCHAR(128) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');

    -- 处理新增列动作
    INSERT INTO ColumnChanges (DatabaseName, SchemaName, TableName, ColumnName, ActionType, NewDataType)
    SELECT
        @DatabaseName,
        @SchemaName,
        @TableName,
        col.value('(Name)[1]', 'NVARCHAR(128)'),
        'ADD',
        col.value('(DataTypeWithCollation)[1]', 'NVARCHAR(100)')
    FROM @EventData.nodes('/EVENT_INSTANCE/AlterTableActionList/Add/Columns/Column') AS cols(col);

    -- 处理修改列动作
    INSERT INTO ColumnChanges (DatabaseName, SchemaName, TableName, ColumnName, ActionType, OldDataType, NewDataType)
    SELECT
        @DatabaseName,
        @SchemaName,
        @TableName,
        col.value('(Name)[1]', 'NVARCHAR(128)'),
        'ALTER',
        col.value('(OldDataTypeWithCollation)[1]', 'NVARCHAR(100)'),
        col.value('(NewDataTypeWithCollation)[1]', 'NVARCHAR(100)')
    FROM @EventData.nodes('/EVENT_INSTANCE/AlterTableActionList/Alter/Columns/Column') AS cols(col);
END;

优势 #

• 支持单次ALTER TABLE操作中变更多个列
• 准确区分新增/修改动作
• 直接从EVENTDATA提取新旧数据类型，避免表结构变更后读取错误信息
• 包含Schema名称，解决同表名不同Schema的混淆问题

二、SQL Server原生审计功能 #

无需自定义代码，利用SQL Server内置审计追踪DDL变更：

1. 创建服务器审计对象 #

CREATE SERVER AUDIT [ColumnChangeAudit]
TO FILE (FILEPATH = 'C:\SQLAudit\', MAXSIZE = 100 MB)
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT [ColumnChangeAudit] WITH (STATE = ON);

注意：文件路径需确保SQL Server服务账户拥有读写权限

2. 创建数据库审计规范 #

USE YourDatabaseName; -- 替换为目标数据库名
CREATE DATABASE AUDIT SPECIFICATION [ColumnChangeAuditSpec]
FOR SERVER AUDIT [ColumnChangeAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);

3. 查询审计日志 #

SELECT
    event_time AS 变更时间,
    server_principal_name AS 操作人,
    database_name AS 数据库名,
    schema_name AS 架构名,
    object_name AS 表名,
    statement AS 执行语句
FROM sys.fn_get_audit_file('C:\SQLAudit\ColumnChangeAudit_*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'AL' -- ALTER TABLE动作ID
AND (statement LIKE '%ALTER TABLE%ADD COLUMN%' OR statement LIKE '%ALTER TABLE%ALTER COLUMN%');

优势 #

• 无需自定义开发，支持更全面的审计信息（操作人、执行语句等）
• 企业级稳定性，适合生产环境

三、第三方工具 #

如果需要可视化追踪或更强大的变更管理能力，可选用以下工具：

• Redgate SQL Compare：对比不同版本的数据库Schema，追溯列的创建与修改历史
• ApexSQL Audit：专注于数据库审计，支持DDL变更的实时监控与历史查询
• SolarWinds Database Performance Analyzer：附带Schema变更监控模块，结合性能分析展示变更影响

关于已存在列的创建时间 #

SQL Server默认不存储已存在列的创建时间，若未提前开启监控（触发器/审计），只能通过以下方式尝试获取：

• 从数据库历史备份中恢复，查询备份中的sys.columns视图
• 查找数据库的DDL执行历史（如应用发布记录、DBA操作日志）

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