How to find stored procedure name using column name in Oracle 11g

Question

I have hundreds of stored procedures, and I want to find out the name of the procedure that uses the specific column name in the query

Answer 1

This will do it, but it can create false positives for common name names

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

where OWNER is the schema that stores the stored procedures that you want to find, and COLUMN_NAME is the name of the column you want to find. If you are not using mixed case column names, you can replace the last row with

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

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

Answer 2

There is no guaranteed way, but you can search for user / all / dba_source using regexp_like to check for whole words and cross-reference to narrow down the list of packages to check with user / all / dba_dependencies.

 select s.name, s.type, s.line, s.text from user_source s where ltrim(s.text,chr(9)||' ') not like '--%' and regexp_like(lower(s.text),'\Wyour_column_name_here\W') and (s.name, s.type) in ( select d.name, d.type from user_dependencies d where d.referenced_owner = user and d.referenced_name = 'YOUR_TABLE_NAME_HERE' ); 

or if links can be linked to other schemes,

 select s.owner, s.name, s.type, s.line, s.text from all_source s where ltrim(s.text,chr(9)||' ') not like '--%' and regexp_like(lower(s.text),'\Wyour_column_name_here\W') and (s.owner, s.name, s.type) in ( select d.owner, d.name, d.type from all_dependencies d where d.referenced_owner = user and d.referenced_name = 'YOUR_TABLE_NAME_HERE' ); 

You can simply use select distinct s.owner, s.name, s.type ... to get a list of objects to explore.