Stone Table by WorldWanderlust
To get the table definition.
describe foo;
Or simply
desc foo
To get the constraints of a table.
select * from user_constraints where table_name='foo';
Similarly to find the table for a constraint.
select table_name from user_constraints where constraint_name='bar';
To find all the tables that have foreign key constraints for a given table you can use this query. It first find the name of the unique and/or primary key constraints in the given table. Then it finds all the constraints in other tables that reference this constraint. Note that the table names are usually uppercase.
select owner,constraint_name,constraint_type,table_name,r_owner,
r_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='BAZ');
A simpler way which also constrains the owner would be like this.
select table_name,constraint_name
from all_constraints
where constraint_type='R'
and owner='FRANK'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');
For further reading please see Finding Foreign Key Constraints in Oracle and Oracle Constraints.
