Re: Use SQL to get references(foreign keys)

if user_constraints returns no records there are NO foreign keys defined. Foreign keys are in user_constraints with constraint_type = 'R' For those keys, the primary key constraint it refers to is in r_owner, and r_constraint_name.

select r_owner, r_constraint_name, table_name, column_name from user_constraints c
, user_cons_columns cc
where cc.constraint_name = r_constraint_name and cc. owner = r_owner
and c.table_name = '<your table>'
order by position

Would provide you with the desired results. You remarks about sp_help betray a sqlserver and/or sybase knowledge. Just FYI: Oracle is *not* just another sqlserver clone.

Hth,

Sybrand Bakker, Oracle DBA

<code_1_at_my-deja.com> schreef in berichtnieuws 8fsk6m$aj8$1_at_nnrp1.deja.com...
> I have a field for which I need to check which field in which table it
> references. I am basically after "Field REFERENCES tablename.fieldname."
> How do I do this in SQL (Oracle for Windows95)? The sp_help procedures
> don't work and the user_constraints table returns no records when
> queried.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed May 17 2000 - 00:00:00 CDT