SQL Server 2014中查询Stored Procedure与SQL Functions内表的DML操作

完全可以通过系统元数据查询实现需求，不需要逐个打开上千行的存储过程、函数代码逐行排查。

实现核心逻辑 #

SQL Server 2014 会将所有用户自定义存储过程、SQL函数的完整定义文本存储在系统视图sys.sql_modules中，检索过程完全在元数据层面完成，不受模块代码长度的影响。
检索的匹配规则逻辑非常直接：

• 定位所有引用了目标表的存储过程、函数对象
• 过滤掉仅对目标表做SELECT查询的场景
• 识别对象定义中紧邻目标表名的INSERT/UPDATE/DELETE/MERGE类DML关键字，标记对应的操作类型

可直接复用的查询语句 #

替换代码中的目标表名、所属架构参数，即可直接在SSMS中运行获取结果：

-- 配置需要排查的目标表信息
DECLARE @TargetTableSchema NVARCHAR(128) = 'dbo';
DECLARE @TargetTableName NVARCHAR(128) = 'Customer';

SELECT
    o.name AS ObjectName,
    CASE o.type_desc
        WHEN 'SQL_STORED_PROCEDURE' THEN '存储过程'
        WHEN 'SQL_SCALAR_FUNCTION' THEN '标量函数'
        WHEN 'SQL_INLINE_TABLE_VALUED_FUNCTION' THEN '内联表值函数'
        WHEN 'SQL_TABLE_VALUED_FUNCTION' THEN '多语句表值函数'
    END AS ObjectType,
    -- 聚合匹配到的所有DML操作类型
    STUFF((
        SELECT DISTINCT ', ' + DMLType
        FROM (
            SELECT
                CASE
                    WHEN m.definition LIKE '%INSERT%' + QUOTENAME(@TargetTableSchema) + '.' + QUOTENAME(@TargetTableName) + '%' THEN 'INSERT'
                    WHEN m.definition LIKE '%UPDATE%' + QUOTENAME(@TargetTableSchema) + '.' + QUOTENAME(@TargetTableName) + '%' THEN 'UPDATE'
                    WHEN m.definition LIKE '%DELETE%' + QUOTENAME(@TargetTableSchema) + '.' + QUOTENAME(@TargetTableName) + '%' THEN 'DELETE'
                    WHEN m.definition LIKE '%MERGE%' + QUOTENAME(@TargetTableSchema) + '.' + QUOTENAME(@TargetTableName) + '%' THEN 'MERGE'
                END AS DMLType
            FROM sys.sql_modules m
            WHERE m.object_id = o.object_id
        ) t
        WHERE DMLType IS NOT NULL
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS MatchedDMLTypes
FROM
    sys.objects o
INNER JOIN
    sys.sql_modules m ON o.object_id = m.object_id
WHERE
    -- 仅筛选用户自定义的存储过程、函数对象
    o.type IN ('P', 'FN', 'IF', 'TF')
    -- 精准匹配带架构前缀的目标表引用，避免跨架构、跨库同名表的误匹配
    AND (
        m.definition LIKE '%' + QUOTENAME(@TargetTableSchema) + '.' + QUOTENAME(@TargetTableName) + '%'
        OR m.definition LIKE '%' + @TargetTableSchema + '.' + @TargetTableName + '%'
    )

落地注意事项 #

• 查询结果会自动过滤仅通过SELECT语句引用目标表的对象，仅返回存在写操作的模块记录
• 针对代码长度1000-1500行的长存储过程，检索速度不会受代码量影响，运行时间通常在秒级
• 检索依赖对象的静态定义文本，如果模块中存在通过sp_executesql执行的动态拼接DML操作，这类动态语句不会被静态文本匹配识别，需要补充扫描动态SQL字符串片段做二次排查
• 对匹配到结果的模块，可通过sp_helptext '模块名'快速定位到匹配DML操作的具体代码行，不需要通读全量代码
• 可通过循环替换参数的方式，一次性批量排查Customer、PurchaseOrder、User等多表的DML依赖关系

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