Re: How to query sys tables for self-joining tables/columns?

On Thu, 16 Aug 2001 15:54:48 -0500, "Randall J. Parr" <RParr_at_TemporalArts.com> wrote:

>
>I am trying to find out how to dynamically discover the existence and definition of such columns in an
>existing database.

Randall, assuming you have a database created with proper definition of foreign key constraints, you can get this info from the dictionary.

DBA_CONSTRAINTS (or USER_CONSTRAINTS or ALL_CONSTRAINTS) tells you which ones of them are for foreign keys (constraint_type = 'R'). IIRC, the same table will tell you which is the other table name (which can be the same name) and its columns involved in the constraint. If you need to know what are the column names in the originating table, then use DBA_CONS_COLUMNS (or USER_CONS_COLUMNS or ALL_CONS_COLUMNS). That will tell you the columns in this table that are related to the ones itemized in DBA_CONSTRAINTS.

The difficulty of course is that not everybody bothers defining these constraints. They build them dynamically at SQL execution time. Which makes it impossible to do what you want. But if yours has constraints defined properly, then no problem.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Fri Aug 17 2001 - 06:35:06 CDT