SQL to search objects, including stored procedures, in Oracle

Glenn Wark picture Glenn Wark · May 12, 2009 · Viewed 133.5k times · Source

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

Chris Cameron-Mills picture Chris Cameron-Mills · May 12, 2009

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.