Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL query for foreign keys?
Stan Brown wrote:
> In <9ji6v7$j2m$1_at_panix2.panix.com> stanb_at_panix.com (Stan Brown) writes:
>
> >Given a table name column name pair, what query can I run against the
> >system tables to find out:
> >1. If it has a foreign key.
> >2. If so, what the table name column name pair that it's key resieds in?
>
> Hi, I had to make a little trip for the past week, or so, and ho usenet
> access during this time.
>
> I am still unable to get this working. Could anyone provide further insigth
> as to how to acomplish this?
>
> What I am trying to do is determinet where to allow free form input, and
> where to force the user to select from a list of existing choices here.
Here's a view I built years ago that did it for a schema named 'OMS2':
SELECT T.owner AS CHILD_OWNER,
T.table_name AS CHILD_TABLE, T.constraint_name AS FOREIGN_KEY_NAME, R.owner AS PARENT_OWNER, R.table_name AS PARENT_TABLE, R.constraint_name AS PARENT_CONSTRAINTFROM dba_constraints T, dba_constraints R
WHERE T.r_constraint_name = R.constraint_name AND T.r_owner = R.owner AND T.constraint_type='R' AND R.owner = 'OMS2' AND T.owner = 'OMS2';
With a small amount of modification I've no doubt you can make this work.
Daniel A. Morgan Received on Tue Jul 31 2001 - 13:13:45 CDT