如何查询SQL Server中已执行的表DDL操作及关联用户

Alright, let's break down how you can track who modified table structures and view all DDL operations for a specific table in SQL Server. There are a couple of approaches depending on whether you already have auditing in place or need to set it up for future changes.

一、利用默认跟踪查询历史DDL操作及关联用户 #

SQL Server默认开启了默认跟踪，它会捕获包括关键DDL操作在内的一系列系统事件。你可以查询这个跟踪文件来获取表结构变更的历史数据。

以下是查询目标表所有相关DDL事件的SQL语句：

DECLARE @TargetTableName NVARCHAR(128) = 'YourTableName'; -- 替换为你的实际表名

SELECT
    te.name AS EventType,
    t.DatabaseName,
    t.ObjectName AS TableName,
    t.LoginName,
    t.ApplicationName,
    t.StartTime AS ChangeTime,
    t.TextData AS ExecutedSQL
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), (SELECT value FROM sys.fn_trace_getinfo(0) WHERE property = 2)), DEFAULT) AS t
JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
WHERE
    t.ObjectName = @TargetTableName
    AND te.name IN ('Alter Table', 'Create Table', 'Drop Table', 'Alter Index', 'Create Index', 'Drop Index') -- 可按需添加更多DDL事件类型
ORDER BY t.StartTime DESC;

说明： #

• sys.fn_trace_getinfo(0) 用于获取当前默认跟踪文件的路径，DEFAULT 参数表示让SQL Server读取所有滚动生成的跟踪文件。
• 我们过滤了修改表结构或索引的事件，并限定到目标表。
• 结果包含变更类型、执行操作的登录名、变更时间以及实际执行的SQL命令。

⚠️ 注意：默认跟踪有大小限制，当达到最大文件尺寸时会覆盖旧数据。如果需要长期保留记录，建议改用扩展事件或DDL触发器。

二、创建DDL触发器记录未来的表结构修改 #

如果默认跟踪没有你需要的历史数据，或者你想确保未来的变更被永久记录，可以创建一个DDL触发器配合日志表来实现。

步骤1：创建用于存储DDL事件的日志表 #

CREATE TABLE DDLChangeAuditLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100) NOT NULL,
    ChangeDateTime DATETIME DEFAULT GETDATE() NOT NULL,
    LoginName NVARCHAR(128) NOT NULL,
    DatabaseUserName NVARCHAR(128) NOT NULL,
    DatabaseName NVARCHAR(128) NOT NULL,
    AffectedObjectName NVARCHAR(128) NOT NULL,
    ExecutedSQL NVARCHAR(MAX) NOT NULL
);

步骤2：创建捕获变更的DDL触发器 #

CREATE TRIGGER TrackTableStructureChanges
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO DDLChangeAuditLog (EventType, LoginName, DatabaseUserName, DatabaseName, AffectedObjectName, ExecutedSQL)
    SELECT
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)'),
        EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]', 'NVARCHAR(128)'),
        EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(128)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
END;

步骤3：查询目标表的变更日志 #

SELECT * 
FROM DDLChangeAuditLog 
WHERE AffectedObjectName = 'YourTableName' 
ORDER BY ChangeDateTime DESC;

三、辅助方法：通过系统视图关联可能的修改用户 #

这个方法不如跟踪/触发器数据可靠，但你可以将表的最后修改时间与活跃会话关联，找到潜在的操作用户（仅适用于变更发生时间较近的情况）：

DECLARE @TargetTableName NVARCHAR(128) = 'YourTableName';

SELECT
    t.name AS TableName,
    t.modify_date AS LastChangeTime,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.tables t
LEFT JOIN sys.dm_exec_sessions s ON s.last_request_end_time >= t.modify_date
WHERE t.name = @TargetTableName
ORDER BY t.modify_date DESC;

⚠️ 提示：只有当执行变更的会话仍处于活跃状态时，这个方法才有效。如果用户在变更后断开连接，将无法得到准确结果。

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