无需手动查看语法，如何自动识别存储过程类型：查询类/数据修改类？

嘿，这问题问得太戳痛点了——谁愿意对着几十上百个存储过程挨个翻代码啊😉 不用手动逐条查看语法当然可行，核心思路就是利用数据库自带的系统元数据视图，直接批量检索存储过程的定义或操作类型，下面分主流数据库给你具体方案：

数据库会自动维护记录所有对象元数据的系统表/视图，我们只需要写查询语句，就能批量筛选出仅含SELECT的报表类存储过程，以及会修改数据的存储过程。

1. SQL Server 方案 #

方法一：直接检索定义中的修改关键字 #

SQL Server的sys.sql_modules存储了所有模块（包括存储过程）的定义文本，我们可以通过关键字匹配快速分类：

SELECT 
    p.name AS procedure_name,
    CASE 
        WHEN sm.definition LIKE '%INSERT%' 
             OR sm.definition LIKE '%UPDATE%' 
             OR sm.definition LIKE '%DELETE%'
             OR sm.definition LIKE '%MERGE%'
             OR sm.definition LIKE '%TRUNCATE TABLE%'
        THEN '数据修改类'
        ELSE '报表查询类（仅SELECT）'
    END AS procedure_type
FROM 
    sys.procedures p
JOIN 
    sys.sql_modules sm ON p.object_id = sm.object_id
ORDER BY 
    procedure_type, procedure_name;

注意：如果存储过程用了动态SQL（比如EXEC(@sql)），这种方法可能漏判，因为动态SQL的内容不会直接出现在definition字段里。

方法二：通过依赖关系判断（更适配动态SQL场景） #

用sys.dm_sql_referenced_entities查看存储过程是否实际修改了引用的表，准确率更高：

SELECT 
    DISTINCT p.name AS procedure_name,
    CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM sys.dm_sql_referenced_entities(p.name, 'OBJECT') re
            JOIN sys.objects o ON re.referenced_id = o.object_id
            WHERE re.is_updated = 1
        ) THEN '数据修改类'
        ELSE '报表查询类（仅SELECT）'
    END AS procedure_type
FROM 
    sys.procedures p
ORDER BY 
    procedure_type, procedure_name;

2. MySQL 方案 #

MySQL的INFORMATION_SCHEMA.ROUTINES表存储了存储过程的定义，同样可以通过关键字筛选：

SELECT 
    ROUTINE_NAME AS procedure_name,
    CASE 
        WHEN ROUTINE_DEFINITION LIKE '%INSERT%'
             OR ROUTINE_DEFINITION LIKE '%UPDATE%'
             OR ROUTINE_DEFINITION LIKE '%DELETE%'
             OR ROUTINE_DEFINITION LIKE '%REPLACE%'
        THEN '数据修改类'
        ELSE '报表查询类（仅SELECT）'
    END AS procedure_type
FROM 
    INFORMATION_SCHEMA.ROUTINES
WHERE 
    ROUTINE_TYPE = 'PROCEDURE'
ORDER BY 
    procedure_type, procedure_name;

动态SQL场景需要额外注意，比如检查是否包含PREPARE/EXECUTE关键字，但这种情况很难100%精准，建议结合人工抽查。

3. PostgreSQL 方案 #

PostgreSQL用pg_proc系统表，结合pg_get_functiondef获取存储过程定义：

SELECT 
    proname AS procedure_name,
    CASE 
        WHEN pg_get_functiondef(oid) LIKE '%INSERT%'
             OR pg_get_functiondef(oid) LIKE '%UPDATE%'
             OR pg_get_functiondef(oid) LIKE '%DELETE%'
             OR pg_get_functiondef(oid) LIKE '%MERGE%'
        THEN '数据修改类'
        ELSE '报表查询类（仅SELECT）'
    END AS procedure_type
FROM 
    pg_proc
WHERE 
    prokind = 'p' -- 筛选存储过程（p表示procedure，f表示function）
ORDER BY 
    procedure_type, procedure_name;

• 嵌套调用的情况：如果存储过程本身没有修改语句，但调用了其他修改数据的存储过程，关键字查询会漏判，需要结合依赖关系表（比如SQL Server的sys.sql_dependencies）进一步排查。
• 注释干扰：如果存储过程的注释里包含INSERT这类关键字，会导致误判，可尝试用正则匹配排除注释内容（不同数据库正则语法有差异）。
• 最终验证：批量查询后，建议抽查几个结果，尤其是标记为“数据修改类”的，确保分类准确。

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