betweenGo

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.

Related posts:

1. Listing All Tables
2. How to Alter Table
3. HOWTO View Tables and Indexes in an Oracle Database
4. Best Practices for Creating Tables
5. HOWTO Really Delete Tables On Oracle 10g