How do I find all code, triggers from an oracle database that relate to specific tables?

Question

I have a problem where I need to remove all code and triggers from a database that relate to certain tables in order for a Solaris package to install. Long complicated story but I need to start with a clean slate.

I've managed to remove all the existing tables/synonyms, but how to locate the code/triggers from sqlplus that is related?

Unfortunately, it's not feasible to drop the database and recreate it.

Answer 1

You should be able to query the system table ALL_TRIGGERS to find the triggers. It has a table_name column. You can probably find the other related objects with different system tables (been awhile since I've messed with Oracle).

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2107.htm

Answer 2

Well, it turns out all the table names are prefixed with my module name DAP.

So, to find all the table names and public synonyms with sqlplus:

select table_name from all_tables where table_name like 'DAP%';
select synonym_name from all_synonyms where table_name like 'DAP%';

To get a list of triggers and sequences

select trigger_name from all_triggers where table_name like 'DAP%';
select sequence_name from all_sequences where sequence_name like 'DAP%';

To get a list of all the constraints

select table_name, constraint_name from all_constraints where table_name like 'DAP%';

To get the DAP related code:

select text from dba_source where name like 'DAP%';

I can now write a script that drops everything.