如何在Azure SQL Database中存储CREATE、TRUNCATE、DROP语句执行历史？

针对Azure SQL Database捕获CREATE、TRUNCATE、DROP这类DDL语句的执行记录，以下是几种实用方案：

方法1：扩展事件（Extended Events） #

这是Azure SQL中轻量高效的监控方案，能精准筛选并捕获DDL操作，性能开销极低。

配置步骤： #

-- 创建事件会话，捕获DDL语句
CREATE EVENT SESSION [CaptureDDLOperations] ON DATABASE 
ADD EVENT sqlserver.ddl_statement_completed(
    ACTION(sqlserver.sql_text, sqlserver.username, sqlserver.client_app_name)
    WHERE (
        statement LIKE '%CREATE%' 
        OR statement LIKE '%TRUNCATE%' 
        OR statement LIKE '%DROP%'
    )
)
ADD TARGET package0.event_file(SET filename=N'DDLCapture.xel')
WITH (STARTUP_STATE=OFF);

-- 启动事件会话
ALTER EVENT SESSION [CaptureDDLOperations] ON DATABASE STATE = START;

查询捕获的记录： #

SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS 执行时间,
    event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(100)') AS 执行用户,
    event_data.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(100)') AS 客户端应用,
    event_data.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)') AS DDL语句
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('DDLCapture*.xel', NULL, NULL, NULL)
) AS x
ORDER BY 执行时间 DESC;

方法2：SQL Server Audit #

适合合规审计场景，可将日志持久化到Azure存储账户或Log Analytics，满足长期留存需求。

配置步骤： #

-- 创建服务器级审核（存储到Azure存储）
CREATE SERVER AUDIT [DDLAudit]
TO URL (PATH = 'https://your-storage-account.blob.core.windows.net/audit-logs/')
WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);

-- 创建数据库级审核规范，捕获所有Schema对象变更
CREATE DATABASE AUDIT SPECIFICATION [DDLAuditSpec]
FOR SERVER AUDIT [DDLAudit]
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);

-- 启用服务器审核
ALTER SERVER AUDIT [DDLAudit] WITH (STATE = ON);

查询审计日志： #

SELECT 
    event_time AS 执行时间,
    session_server_principal_name AS 执行用户,
    statement AS DDL语句
FROM sys.fn_get_audit_file('https://your-storage-account.blob.core.windows.net/audit-logs/*.xel', DEFAULT, DEFAULT)
WHERE action_id IN ('CR', 'DL', 'TC') -- 对应CREATE、DROP、TRUNCATE类操作
ORDER BY 执行时间 DESC;

方法3：DDL触发器 #

适合自定义记录需求，直接在数据库内将操作写入自定义日志表，灵活可控但性能开销略高。

配置步骤： #

-- 先创建日志表
CREATE TABLE DDLChangeLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    ChangeTime DATETIME2 DEFAULT GETDATE(),
    Username NVARCHAR(100),
    DDLStatement NVARCHAR(MAX),
    EventType NVARCHAR(50)
);

-- 创建DDL触发器，覆盖目标操作
CREATE TRIGGER CaptureDDLTrigger
ON DATABASE
FOR CREATE_TABLE, TRUNCATE_TABLE, DROP_TABLE, CREATE_PROCEDURE, DROP_PROCEDURE, CREATE_INDEX, DROP_INDEX
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO DDLChangeLog (Username, DDLStatement, EventType)
    VALUES (
        SUSER_SNAME(),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)'),
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(50)')
    );
END;

查询记录： #

SELECT * FROM DDLChangeLog ORDER BY ChangeTime DESC;

方案对比 #

• 扩展事件：性能最优，适合实时监控和调试，日志可按需清理
• SQL Audit：合规性强，日志持久化存储，支持集中分析
• DDL触发器：自定义程度高，但仅能覆盖指定DDL事件，高并发场景需评估性能影响

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