SQL to search objects, including stored procedures, in Oracle

Question

I need to write some sql that will allow me to query all objects in our Oracle database. Unfortunately the tools we are allowed to use don't have this built in. Basically, I need to search all tables, procedures, triggers, views, everything.

I know how to search for object names. But I need to search for the contents of the object. i.e. SELECT * FROM DBA_OBJECTS WHERE object_name = '%search string%';

Thanks, Glenn

Answer 1

I'm not sure I quite understand the question but if you want to search objects on the database for a particular search string try:

SELECT owner, name, type, line, text  FROM dba_source WHERE instr(UPPER(text), UPPER(:srch_str)) > 0; 

From there if you need any more info you can just look up the object / line number.

For views you can use:

SELECT * FROM dba_views WHERE instr(UPPER(text_vc), UPPER(:srch_str)) > 0 

Answer 2

i'm not sure if i understand you, but to query the source code of your triggers, procedures, package and functions you can try with the "user_source" table.

select * from user_source