For any given table 'foo', I want a question to develop a group of tables which have foreign secrets that could indicat foo. I am using Oracle 10G.

This will work (or something like that close):

select table_name
from all_constraints
where constraint_type='R'
and r_constraint_name in 
  (select constraint_name
  from all_constraints
  where constraint_type in ('P','U')
  and table_name='<your table here>');

Download the Oracle Reference Guide for 10G which describes the information dictionary tables.

The solutions above are great but browse the other tables which might connect with constraints.

choose * from dict where table_title like '%CONS%'

Finally, obtain a tool like Toad or SQL Developer which enables you to definitely browse these items inside a UI, you have to learn how to make use of the tables but you need to use a UI also.

connect to Oracle Database Online Documentation

You might want to explore the Data Dictionary views. They've the prefixes:

  • User
  • All
  • DBA

sample:

select * from dictionary where table_name like 'ALL%'


Ongoing Mike's example, you might want to generate scripts make it possible forOreliminate the restrictions. I only modified the 'select' within the first row.

select  'alter table ' || TABLE_NAME || ' disable constraint ' || CONSTRAINT_NAME || ';'
from all_constraints
where constraint_type='R'
and r_constraint_name in 
  (select constraint_name
  from all_constraints
  where constraint_type in ('P','U')
  and table_name='<your table here>');

The next statement should provide the children and every one of their descendents. I've examined it with an Oracle 10 database.

Choose level, primary.table_title parent, link.table_title child FROM user_constraints primary, user_constraints link WHERE primary.constraint_type IN ('P', 'U') AND link.r_constraint_title= primary.constraint_title Begin With primary.table_title LIKE UPPER('&lifier&table_name') CONNECT BY primary.table_title = PRIOR link.table_title ORDER BY level, primary.table_title, link.table_title