如何在SQL Server中查询存储过程的完整修改历史

SQL Server存储过程全量修改记录查询方案 #

原生SQL Server的系统视图sys.objects仅保留存储过程的创建时间和最近一次修改时间，不会默认存储历史修改记录、修改人以及具体变更内容，你可以根据自身环境的配置选择对应的查询方式： #

场景1：仅需查询最近一次修改的修改人（未开启额外追踪功能时）

默认情况下SQL Server会开启默认追踪（Default Trace），只要未手动关闭，你可以通过以下语句查询到目标存储过程最近一次修改的操作人、修改时间：

SELECT
    DB_NAME() AS [database name],
    SCHEMA_NAME(o.schema_id) AS [schema name],
    o.name AS [stored proc name],
    o.create_date AS [create date],
    o.modify_date AS [last modify date],
    te.name AS [operation type],
    login_name AS [LastModifiedBy],
    start_time AS [LastModifiedTime]
FROM sys.trace_events te
INNER JOIN sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
    (SELECT TOP 1 value FROM sys.fn_trace_getinfo(NULL) WHERE property = 2)), DEFAULT) gt
ON te.trace_event_id = gt.EventClass
INNER JOIN sys.objects o ON gt.ObjectID = o.object_id
WHERE 
    o.type = 'P' 
    AND o.name = 'spName'
    AND te.name IN ('Object:Altered','Object:Created')
ORDER BY start_time DESC

注意：默认追踪的日志是滚动覆盖的，一般仅保留数天到数周的记录，超出时间范围的历史数据无法查询 #

场景2：需要查询全量历史修改记录

如果需要查询所有历史修改记录、具体变更内容，需要确认你所在环境是否提前开启了以下任意一种追踪能力：

• 开启了SQL Server审计（Audit）功能：可直接从审计日志视图查询对应存储过程的所有修改事件、操作人、操作时间和变更内容
• 部署了DDL触发器记录存储过程变更：如果之前已经创建了DDL触发器将CREATE_PROCEDURE/ALTER_PROCEDURE/DROP_PROCEDURE事件写入自定义日志表，直接查询自定义日志表即可获取全量修改记录

如果之前未开启任何追踪功能，无法回溯历史修改记录，你可以通过以下DDL触发器实现后续的修改记录留存：

-- 先创建自定义日志表
CREATE TABLE dbo.ProcChangeLog
(
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    EventType NVARCHAR(100),
    ObjectName NVARCHAR(200),
    ObjectSchema NVARCHAR(100),
    ModifiedTime DATETIME DEFAULT GETDATE(),
    ModifiedBy NVARCHAR(200) DEFAULT SUSER_SNAME(),
    ChangeContent NVARCHAR(MAX) -- 存储变更的SQL语句
)
GO
-- 创建DDL触发器
CREATE TRIGGER trg_ProcChangeTrace
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    DECLARE @EventData XML = EVENTDATA()
    INSERT INTO dbo.ProcChangeLog(EventType, ObjectName, ObjectSchema, ChangeContent)
    VALUES
    (
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(200)'),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')
    )
END
GO

后续需要查询时直接查询dbo.ProcChangeLog表即可拿到所有存储过程的修改人、修改时间、具体变更SQL内容。

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