Oracle Table Usage Across Stored Procedures

Question

I have a 11G database. I need to examine a number of stored procedures to see if they use a particular table. (Both front end and back end sps) I have full access to the database, and I also have a copy of all the individual sps for the project which are stored on a TFS.

I would like a way to generate a list of all the sps that interact with this particular table. I'm unfamiliar with how to go about searching for these. Can anyone advise the most logical way of obtaining this data?

Thanks.

Answer 1

If I understand this correctly, you're trying to search for occurrence of a table in all stored procs. In that case, you can use this query:

When searching for occurrences of SP in your schema

SELECT * FROM user_source WHERE text LIKE '%tab_name%';

When searching for occurrences of SP in all schemas

SELECT * FROM all_source WHERE text LIKE '%tab_name%';