如何在Oracle 11g中使用列名查找存储过程的名称 [英] How to find name of the stored procedure using Column name in Oracle 11g

问题描述

我有数百个存储过程，我想找出在查询中使用特定列名的过程的名称

I have hundreds of stored procedures and i want to find out the name of the procedure which uses the particular column name in query

推荐答案

这样做可以，但是可能会为通用列名产生误报

This will do it, but might produce false positives for generic column names

SELECT DISTINCT type, name
FROM dba_source
WHERE owner = 'OWNER'
AND text LIKE '%COLUMN_NAME%';

其中OWNER是拥有要搜索的存储过程的架构，而COLUMN_NAME是要查找的列名称.如果您不使用大小写混合的列名，则可以将最后一行替换为

where OWNER is the schema which owns the stored procedures you want to search and COLUMN_NAME is the column name that you want to find. If you don't use mixed case column names then you can replace the last line with

AND UPPER(text) LIKE '%COLUMN_NAME%';

，然后以大写字母输入列名，以进行不区分大小写的搜索.

and enter the column name in capitals to get a case insensitive search.

这篇关于如何在Oracle 11g中使用列名查找存储过程的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！