sql statement

From: Eric Givler <egivler_at_flash.net>
Date: Sun, 14 Jan 2001 04:16:26 GMT
Message-ID: <uK986.4092$J%.466296@news.flash.net>

Does anyone have a sqlstatement that when run would show a foreign key and the table it points to? (I'd like to list all columns and be ordered by position if there are more than one column in the key.

I tried:

select fk1.table_name||'.'||fkc1.column_name fk_name,

          fkc1.position,
          fk1.r_owner||'.'||pk1.table_name||'.'||pkc1.collumn_name pk_name
from user_constraints fk1,
        user_cons_columns fkc1,
        dba_constraints pk1,
        dba_cons_Columns pkc1

where fk1.constraint_name=upper('&constraint')

   and fk1.constraint_type='R' /* foreign key */
   and fk1.constraint_name=fkc1.constraint_name
   and fk1.table_name=fkc1.table_name
   and fk1.owner=fkc1.owner
   and fk1.r_owner = pk1.owner
   and fk1.r_constraint_name=pk1.constraint_name
   and pk1.owner = pkc1.owner
   and pk1.table_name=pkc1.table_name
   and pk1.constraint_name=pkc1.constraint_name

order by fkc1.position

But it just sat there.... Am I missing something or being just plain stupid? Received on Sat Jan 13 2001 - 22:16:26 CST