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

Typically you would look for foreign key constraints defined between columns in the same table. Start by looking in user_constraints and/or all_constraints. Be aware that some databases/dba's don't bother to formally declare foreign key relationships at all or are not rigorous about declaring every one.

hth,
Gene Hubert
Durham, NC

"Randall J. Parr" <RParr_at_TemporalArts.com> wrote in message news:<3B7C3317.9D229B95_at_TemporalArts.com>...
> I am trying to discover which set of system tables and/or view (ala ALL_TAB_COLUMNS) would be best suited
> to discovering which tables contained a column which joined to another column in that same table.
>
> I know how to create and use a column which joins to another column in the same table (ala EMP -->
> MANAGER).
>
> I am trying to find out how to dynamically discover the existence and definition of such columns in an
> existing database.
>
> I am trying to find this information because my utilities which export (and import) the database to
> XML format files need to export files with "self-joins" topologically (aka heirarchically).
>
> I have the topological / heirarchical export working fine but currently have to explicitly invoke it on
> tables which I know to contain self-joins. I want to automate the discovery and invocation of this by
> querying the database system tables.
>
> R.Parr
> Temporal Arts.
>
> Gene Hubert wrote:
>
> > You can join any table to itself on any column but it sounds like you
> > mean something different by "self join". If you could provide more
> > info you might get some useful answers.
> >
> > Gene Hubert
> > Durham, NC
> >
> > ----------
> >
> > "Randall J. Parr" <RParr_at_TemporalArts.com> wrote in message news:<3B707C15.A5E561F7_at_TemporalArts.com>...
> > > Is it possible, and if so how, to query the system tables of 8.1.5+ to find
> > > tables with a self-join and the self-joining columns?
> > >
> > > Thanks
> > > R.Parr
> > > Temporal Arts
Received on Fri Aug 17 2001 - 08:39:33 CDT