Re: SMI query for Index column names
the solution would appear to be a union across all the part1--part16
where the value is not zero.
As in:
select '1',c.colname from systables t, sysindexes x,
syscolumns c where t.tabid=x.tabid and
x.tabid=c.tabid and t.tabname='items' and
c.colno=x.part1
union
select '2',c.colname from systables t, sysindexes x,
syscolumns c where t.tabid=x.tabid and
x.tabid=c.tabid and t.tabname='items' and
c.colno=x.part2 and x.part2 <> 0
union
select '3',c.colname from systables t, sysindexes x,
syscolumns c where t.tabid=x.tabid and
x.tabid=c.tabid and t.tabname='items' and
c.colno=x.part3 and x.part3 <> 0
.
and so forth.
the number literal, btw, lets you know what level in the index the
column is.
sanjeev sagar wrote:
> Hey Guy's,
>
> IDS 7.30 UC2
> O/S-Solaris 2.5.1
>
> I need to find out the columns of all the indexes on
> a table. I tried this
>
> select c.colname from systables t, sysindexes x,> syscolumns c where t.tabid=x.tabid and
> x.tabid=c.tabid and t.tabname='items' and
> c.colno=x.part1
>
> This gives me the head of the indexes. I know that
> sysindexes keep all the colno in part1-part16. I am
> not able to fig out that how to use part1-part16 in
> the query to find out all the columns in all the
> indexes.
>
> Let me put in this way. If you use
> dbaccess->database->tables->indexes
> it shows you all the indexcolumn names for all the
> indexes.
>
> How can i get this from SMI query.
>
> Thanks
>
> Sanjeev
>
> _________________________________________________________
> DO YOU YAHOO!?
> Get your free @yahoo.com address at http://mail.yahoo.com