Microsoft SQL存储过程中如何判断指定列是否被更新

前置要求 #

提前确认两个核心校验参数：

• 目标表完整名称，比如dbo.Order
• 需要检查是否必须更新/插入的列名，比如LastModifyTime、ModifyUserId

方案1：文本快速匹配法 #

适合小范围快速筛查，实现简单，缺点是会误识别注释、字符串常量里的SQL语句。
你可以直接执行以下T-SQL查询，替换对应参数即可拿到疑似漏更的存储过程列表：

DECLARE @TargetTableName SYSNAME = 'dbo.你的目标表名' -- 替换为实际表名
DECLARE @CheckColumnName SYSNAME = '你的指定列名' -- 替换为要检查的列名

SELECT 
    p.name AS 存储过程名称,
    m.definition AS 存储过程代码
FROM sys.procedures p
INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE 
    -- 匹配对目标表的INSERT/UPDATE操作
    (m.definition LIKE '%UPDATE%' + @TargetTableName + '%' OR m.definition LIKE '%INSERT%INTO%' + @TargetTableName + '%')
    -- 排除已写了目标列的存储过程
    AND m.definition NOT LIKE '%' + @CheckColumnName + '%'

如果需要检查多个列，可以在NOT LIKE条件后追加对应列的判断逻辑。

方案2：系统依赖解析法（准确率更高） #

调用SQL Server内置的语句解析函数识别实际生效的对象引用，会自动过滤注释、字符串里的内容，误判率更低。
执行以下T-SQL即可：

DECLARE @TargetTableName SYSNAME = 'dbo.你的目标表名' -- 替换为实际表名
DECLARE @CheckColumnName SYSNAME = '你的指定列名' -- 替换为要检查的列名

CREATE TABLE #TempProcReference (
    ProcName SYSNAME,
    ReferencedEntity SYSNAME,
    ReferencedColumn SYSNAME,
    IsUpdate BIT
)

-- 遍历所有存储过程提取引用关系
INSERT INTO #TempProcReference
EXEC sp_MSforeachdb 'USE ?; 
IF DB_ID() = DB_ID(''你的数据库名'') -- 替换为实际数据库名
BEGIN
    SELECT 
        p.name AS ProcName,
        referenced_entity_name AS ReferencedEntity,
        referenced_minor_name AS ReferencedColumn,
        is_updated AS IsUpdate
    FROM sys.procedures p
    CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(SCHEMA_NAME(p.schema_id)) + ''.'' + QUOTENAME(p.name), ''OBJECT'') r
    WHERE referenced_class = 1 AND referenced_minor_id > 0
END'

-- 查询存在更新目标表但未更新指定列的存储过程
SELECT DISTINCT ProcName AS 存储过程名称
FROM #TempProcReference t1
WHERE 
    t1.ReferencedEntity = PARSENAME(@TargetTableName,1)
    AND t1.IsUpdate = 1
    AND NOT EXISTS (
        SELECT 1 FROM #TempProcReference t2 
        WHERE t2.ProcName = t1.ProcName 
        AND t2.ReferencedColumn = @CheckColumnName
    )

DROP TABLE #TempProcReference

校验注意事项 #

• 上述两种方法均无法识别存储过程内的动态SQL语句，如果你的存储过程存在拼接SQL执行的逻辑，需要单独人工核对
• 如果INSERT语句使用INSERT INTO 表 SELECT * FROM的写法，会被判定为未显式写指定列，需要人工确认SELECT结果集是否包含目标列
• 多表关联UPDATE的场景，需要二次核对SET子句是否确实没有给目标表的指定列赋值

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