Oracle中如何查找存储过程内包含指定列的表名列表

我需要找出存储过程SP_SLD_GEN_REINV_DET_FI中出现的所有表，且这些表必须包含名为C_CURR的列。我尝试了以下两条查询语句，但都没有得到结果：

select atc.TABLE_NAME from ALL_TAB_COLS atc join  all_SOURCE als on atc.table_name
 like '%'||als.text||'%' and als.name = 'SP_SLD_GEN_GIC_REINV_DET_FI' where
atc.COLUMN_NAME = 'C_CURR';

select atc.TABLE_NAME 
from ALL_TAB_COLS atc where atc.COLUMN_NAME = 'C_CURR'
and atc.table_name like (select '%'||als.text||'%' from all_SOURCE als
where als.name = 'SP_SLD_GEN_REINV_DET_FI')
order by atc.TABLE_NAME;

我知道LIKE一般是和固定字符串匹配（比如'%AUTUMN%'），但这里尝试将子查询返回的字符串嵌入%中配合LIKE使用却没成功。想问有没有可行的方法？或者可以用INSTR()来实现？

可行的解决方案 #

你的问题出在匹配逻辑上：all_source的text字段是存储过程的代码行，直接用atc.table_name LIKE '%'||als.text||'%'会把代码里的其他内容（比如注释、变量名）也当成表名匹配，而且子查询返回多行时用LIKE会报错，因为LIKE不能直接匹配多行结果。

可以用以下两种方法实现：

方法1：用INSTR匹配表名出现在存储过程代码中 #

SELECT DISTINCT atc.TABLE_NAME
FROM ALL_TAB_COLS atc
JOIN ALL_SOURCE als 
  ON INSTR(UPPER(als.TEXT), UPPER(atc.TABLE_NAME)) > 0
WHERE atc.COLUMN_NAME = 'C_CURR'
  AND als.NAME = 'SP_SLD_GEN_REINV_DET_FI'
  AND als.TYPE = 'PROCEDURE' -- 限定类型为存储过程，避免同名对象干扰
ORDER BY atc.TABLE_NAME;

• 用UPPER()统一大小写，避免大小写不匹配的问题
• INSTR()判断表名是否出现在存储过程的代码行中
• DISTINCT去重，因为同一张表可能在存储过程的多行代码中出现

方法2：先提取存储过程中的表名，再筛选带指定列的表 #

如果存储过程里的表名格式比较规范（比如没有和变量名重名的情况），可以先通过正则提取表名，再关联ALL_TAB_COLS：

WITH proc_tables AS (
  SELECT DISTINCT 
    REGEXP_SUBSTR(UPPER(als.TEXT), '[A-Z_][A-Z0-9_]*') AS TABLE_NAME
  FROM ALL_SOURCE als
  WHERE als.NAME = 'SP_SLD_GEN_REINV_DET_FI'
    AND als.TYPE = 'PROCEDURE'
    -- 过滤掉非表名的关键词，比如SELECT、FROM等
    AND REGEXP_SUBSTR(UPPER(als.TEXT), '[A-Z_][A-Z0-9_]*') NOT IN ('SELECT', 'FROM', 'WHERE', 'INSERT', 'UPDATE', 'DELETE', 'AND', 'OR')
)
SELECT atc.TABLE_NAME
FROM ALL_TAB_COLS atc
JOIN proc_tables pt ON UPPER(atc.TABLE_NAME) = pt.TABLE_NAME
WHERE atc.COLUMN_NAME = 'C_CURR'
ORDER BY atc.TABLE_NAME;

• 这个方法需要根据实际情况调整正则和过滤的关键词，避免把变量名或关键字当成表名

注意事项 #

• 确保你有ALL_SOURCE和ALL_TAB_COLS的查询权限
• 如果存储过程中使用了别名或者动态SQL，以上方法可能无法完全覆盖，这种情况需要结合动态SQL的内容进一步分析

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