Re: Finding out about indexes from system tables

"Stan Brown" <stanb_at_panix.com> wrote in message news:9j71i1$bpg$1_at_panix6.panix.com...
> In <9j6ope$sf9$1_at_sun-cc204.lut.ac.uk> "Andrew Hardy"
 <nobody_at_spam.from.news.AdvanticaTech.com> writes:
>
>
> >"Stan Brown" <stanb_at_panix.com> wrote in message
> >news:9j6npf$5fq$1_at_panix3.panix.com...
> >> I'm working on a littler PerlTK script to allow some operators to do
> >> lightweight table data changes. I would like to display which fileds in
 the
> >> table they are working on are "keys". At least in our case I can figure
> >> this out by looking at what cols have unique indexes. All of our tables
> >> have unique indexes on thse cols, like:
 

> >You should really be looking at the constraints data dictionary items
> >all_constraints and all_cons_columns to find the primary keys.
 

> >E.G.
 

> >SELECT CC.TABLE_NAME, CC.POSITION, CC.COLUMN_NAME
> >FROM ALL_CONSTRAINTS C
> > , ALL_CONS_COLUMNS CC
> >WHERE CONSTRAINT_TYPE = 'P'
> > AND CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME
> >ORDER BY CC.TABLE_NAME, CC.POSITION
>
>
> Perfect! Thanks for the tip.
>
> One question, tho. What's POSITION ? I'm not certain I need it for what I
> am doing but, since I don't know what it is, I can't be sure.
>
> Thanks for the _FAST_ response.
>

The first column in the constraint is in position 1 and so on.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Jul 19 2001 - 11:53:59 CDT