--show all constraints which point to a table
col owner_tabname for a40
set ver off

select owner||'.'||table_name owner_tabname,
constraint_name,r_constraint_name from DBA_CONSTRAINTS
where(owner,r_constraint_name) in (select owner,constraint_name from
DBA_CONSTRAINTS b where b.owner= '&owner' and b.table_name = '&table');

set ver on

--same as above but different method. Find foreign key constraints
set ver off

select b.owner,b.table_name,b.constraint_name,b.status from
DBA_CONSTRAINTS a,DBA_CONSTRAINTS b where b.r_constraint_name =
a.constraint_name and a.owner = '&p_owner' and a.table_name =
'&p_table';


-----Original Message-----
From: Binosh Mullasserypadman
[mailto:oracledba-ezmlmshield-x19055008.[Email address protected]
Sent: Thursday, May 29, 2008 6:51 AM
To: LazyDBA Discussion
Subject: Query to find tables and relationships

Hi DBA's

Is there any way to find all tables and its reference tables and
the foreign keys and description of these tables; of a user using an sql
command?

Thanks

DaVita Inc.


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page