Re: table relationships

"Jeff" <idont_at_thinkso.com> wrote in message news:<lH3Wc.14$4o.0_at_fed1read01>...
> When you connect to an Oracle database with Access and ADO, is there a way
> to get information on the Oracle table relationships?
>
> Jeff

The following query lists all relationships:

select a.owner||'.'||a.table_name "Referenced Table", b.owner||'.'||b.table_name "Referenced by", b.constraint_name "Foreign Key"
  from all_constraints a, all_constraints b   where b.constraint_type = 'R'
    and a.constraint_name = b.r_constraint_name;

...And the following one lists all columns that compose each foreign key:
SELECT CONSTRAINT_NAME, TABLE_NAME, POSITION, COLUMN_NAME     FROM ALL_CONS_COLUMNS
    ORDER BY CONSTRAINT_NAME, TABLE_NAME, POSITION /

Good luck. Received on Thu Aug 26 2004 - 20:04:36 CEST