Re: SQL to View Table Relationships?

From: Darren John Capper <darren.capper_at_preci-spark.co.uk>
Date: Mon, 26 Jul 1999 14:06:52 +0100
Message-ID: <379C5D69.A562F0AC@preci-spark.co.uk>

Scott,

Try looking at user_constraints.

select table_name, constraint_name, r_constraint_name from user_constraints
where constraint_type = 'R'

and you end up with:

TABLE_NAME    CONSTRAINT_NAME                 R_CONSTRAINT_NAME
--------------------------------------------------------------------------------------

CO_LINE            FK_COLINE_EMEMPLOYEE1     PK_EMEMPLOYEE
CO_LINE            FK_COLINE_EMEMPLOYEE2     PK_EMEMPLOYEE
CO_LINE            FK_COLINE_ITSTAGE                 PK_ITSTAGE
CO_LINE            FK_COLINE_STSTATUS              PK_STSTATUS
CO_LINE            FK_COLINE_UMUOM                 PK_UMUOM

This is useful if you have named your constraints correctly, e.g. co_line has a foreign key to employee.
If the naming convention that you use is not quite as clear then you will probably have to do a bit of digging,
maybe using the R_CONSTRAINT_NAME to look up the master table in user_ind_columns:

SQL> desc user_ind_columns

 Name                            Null?    Type
 ------------------------------- -------- ----
 INDEX_NAME                      NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 COLUMN_NAME                     NOT NULL VARCHAR2(30)
 COLUMN_POSITION                 NOT NULL NUMBER
 COLUMN_LENGTH                   NOT NULL NUMBER

SQL> select table_name
  2 from user_ind_columns where index_name = 'PK_EMEMPLOYEE';

TABLE_NAME

---

EM_EMPLOYEE Hope this helps,

Regards,
Darren

---

Scott Murray wrote:

> I am stumped trying to display the relationships between tables (i.e.,
> which tables have foreign keys to which tables).
>
> I've tried using:
>
> select * from all_tab_columns
> where owner = 'new_demo' [that's my user name]
> ;
>
> and some similar commands, but I keep getting no records found.
>
> Can anyone help me out?
>
> Thanks in advance,
>
> Scott Murray
> smurray_at_officecomp.com
Received on Mon Jul 26 1999 - 08:06:52 CDT