Oracle: search though all stored procs/triggers/other db code?

Question

Is it possible to search through all of the programmatic objects (functions, stored procedures, triggers, etc) across multiple schemas in Oracle?

There are a number of tables that appear to be unused in my code, but I don't want to break anything by removing them without checking first.

Answer 1

It is possible to search through object code-- you'd generally use the DBMS_METADATA package to generate the DDL for the object and then search the CLOB. However, it doesn't sound like that's actually what you want to do.

If you are just trying to figure out whether a table is referenced by any code in your system, you would generally want to use the DBA_DEPENDENCIES view (or ALL_DEPENDENCIES or USER_DEPENDENCIES depending on your privileges and the scope of what you're looking for). Something like

SELECT *
  FROM dba_dependencies
 WHERE referenced_owner = 'SCOTT'
   AND referenced_name  = 'EMP'
   AND referenced_type  = 'TABLE'

will show you everything that depends on the EMP table in the SCOTT schema.

The only time you'd want to search code rather than looking at DBA_DEPENDENCIES would be when you had code that was doing dynamic SQL where the table name was hard-coded. But that's relatively unlikely in practice.

Answer 2

You can search the DBA_SOURCE view:

SELECT *
  FROM dba_source
 WHERE UPPER(text) LIKE '%YOUR_TABLE_NAME%';