如何用T-SQL动态识别数据库中主体重复的冗余存储过程？

核心思路 #

因为information_schema.routines.definition包含存储过程名称，直接对比会被名称差异干扰，所以我们需要先清洗定义文本：移除创建/修改语句里的存储过程名称，再对剩余主体内容做归一化处理（消除空格、换行这类格式差异），最后通过分组找到内容重复的存储过程。

具体T-SQL脚本 #

WITH CleanedProcedureDefinitions AS (
    SELECT
        SCHEMA_NAME(p.schema_id) + '.' + p.name AS FullProcName,
        -- 替换CREATE/ALTER PROCEDURE后的存储过程名称为统一占位符
        CASE
            -- 处理CREATE PROCEDURE语句
            WHEN CHARINDEX('CREATE PROCEDURE ', m.definition) > 0 THEN
                STUFF(
                    m.definition,
                    CHARINDEX('CREATE PROCEDURE ', m.definition) + LEN('CREATE PROCEDURE '),
                    -- 定位名称结束位置（第一个空格或左括号）
                    PATINDEX('%[ (]%', SUBSTRING(m.definition, CHARINDEX('CREATE PROCEDURE ', m.definition) + LEN('CREATE PROCEDURE '), 2000)) - 1,
                    'dbo.PlaceholderProc'
                )
            -- 处理ALTER PROCEDURE语句
            WHEN CHARINDEX('ALTER PROCEDURE ', m.definition) > 0 THEN
                STUFF(
                    m.definition,
                    CHARINDEX('ALTER PROCEDURE ', m.definition) + LEN('ALTER PROCEDURE '),
                    PATINDEX('%[ (]%', SUBSTRING(m.definition, CHARINDEX('ALTER PROCEDURE ', m.definition) + LEN('ALTER PROCEDURE '), 2000)) - 1,
                    'dbo.PlaceholderProc'
                )
            ELSE m.definition
        END AS CleanedDefinition
    FROM sys.procedures p
    JOIN sys.sql_modules m ON p.object_id = m.object_id
),
NormalizedDefinitions AS (
    SELECT
        FullProcName,
        -- 归一化处理：移除换行、回车，合并连续空格为单个空格
        REPLACE(
            REPLACE(
                REPLACE(CleanedDefinition, CHAR(10), ''),
                CHAR(13), ''),
            '  ', ' ') AS NormalizedContent
    FROM CleanedProcedureDefinitions
)
-- 分组筛选内容重复的存储过程
SELECT
    NormalizedContent,
    STRING_AGG(FullProcName, ', ') AS DuplicateProcedures
FROM NormalizedDefinitions
GROUP BY NormalizedContent
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

脚本说明 #

1. 清洗定义：通过STUFF和PATINDEX精准定位CREATE/ALTER PROCEDURE后的存储过程名称，替换为统一占位符，彻底消除名称差异对内容对比的干扰。
2. 内容归一化：移除文本中的换行、回车符号，合并连续空格，避免因格式排版不同导致的误判。
3. 分组检测：按归一化后的内容分组，筛选出分组数量大于1的记录，这些就是名称不同但主体内容完全一致的冗余存储过程。

扩展优化 #

• 如果存储过程内部存在自调用（如EXEC dbo.MyProc），需要额外添加逻辑将内部调用的名称也替换为占位符，避免因内部名称差异导致内容对比失败。
• 若需忽略注释差异，可以补充逻辑移除单行注释（--开头）和多行注释（/* */包裹），但会增加脚本复杂度，需根据实际场景调整。

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