Re: List of All Indexes
Hi David,
be aware that Informix stores "colno" as negative value if index is
created "descending"; therefore you'll lose all descending
index-columns. Use the ABS-function to get all index-columns, like:
select tabname,
idxname,
c1.colname,
c2.colname,
c3.colname,
c4.colname,
c5.colname,
c6.colname,
c7.colname,
c8.colname,
c9.colname,
c10.colname,
c11.colname,
c12.colname,
c13.colname,
c14.colname,
c15.colname,
c16.colname
from systables t,
syscolumns c1,
outer(syscolumns c2),
outer(syscolumns c3),
outer(syscolumns c4),
outer(syscolumns c5),
outer(syscolumns c6),
outer(syscolumns c7),
outer(syscolumns c8),
outer(syscolumns c9),
outer(syscolumns c10),
outer(syscolumns c11),
outer(syscolumns c12),
outer(syscolumns c13),
outer(syscolumns c14),
outer(syscolumns c15),
outer(syscolumns c16),
sysindexes i
where t.tabid > 99
and t.tabtype = 'T'
and t.tabid = i.tabid
and i.tabid = c1.tabid
and i.tabid = c2.tabid
and i.tabid = c3.tabid
and i.tabid = c4.tabid
and i.tabid = c5.tabid
and i.tabid = c6.tabid
and i.tabid = c7.tabid
and i.tabid = c8.tabid
and i.tabid = c9.tabid
and i.tabid = c10.tabid
and i.tabid = c11.tabid
and i.tabid = c12.tabid
and i.tabid = c13.tabid
and i.tabid = c14.tabid
and i.tabid = c15.tabid
and i.tabid = c16.tabid
and part1 = abs(c1.colno)
and part2 = abs(c2.colno)
and part3 = abs(c3.colno)
and part4 = abs(c4.colno)
and part5 = abs(c5.colno)
and part6 = abs(c6.colno)
and part7 = abs(c7.colno)
and part8 = abs(c8.colno)
and part9 = abs(c9.colno)
and part10 = abs(c10.colno)
and part11 = abs(c11.colno)
and part12 = abs(c12.colno)
and part13 = abs(c13.colno)
and part14 = abs(c14.colno)
and part15 = abs(c15.colno)
and part16 = abs(c16.colno)
order by tabname, idxname;
Peter
david.ashby@workcover.nsw.gov.au wrote:
> Rajendra,
>
> Try this sql.
>
>
> Regards
>
>
> David Ashby
>
> select tabname,
> idxname,
> c1.colname,
> c2.colname,
> c3.colname,
> c4.colname,
> c5.colname,
> c6.colname,
> c7.colname,
> c8.colname,
> c9.colname,
> c10.colname,
> c11.colname,
> c12.colname,
> c13.colname,
> c14.colname,
> c15.colname,
> c16.colname
> from systables t,
> syscolumns c1,
> outer(syscolumns c2),
> outer(syscolumns c3),
> outer(syscolumns c4),
> outer(syscolumns c5),
> outer(syscolumns c6),
> outer(syscolumns c7),
> outer(syscolumns c8),
> outer(syscolumns c9),
> outer(syscolumns c10),
> outer(syscolumns c11),
> outer(syscolumns c12),
> outer(syscolumns c13),
> outer(syscolumns c14),
> outer(syscolumns c15),
> outer(syscolumns c16),
> sysindexes i
> where t.tabid > 99
> and t.tabtype = 'T'
> and t.tabid = i.tabid
> and i.tabid = c1.tabid
> and i.tabid = c2.tabid
> and i.tabid = c3.tabid
> and i.tabid = c4.tabid
> and i.tabid = c5.tabid
> and i.tabid = c6.tabid
> and i.tabid = c7.tabid
> and i.tabid = c8.tabid
> and i.tabid = c9.tabid
> and i.tabid = c10.tabid
> and i.tabid = c11.tabid
> and i.tabid = c12.tabid
> and i.tabid = c13.tabid
> and i.tabid = c14.tabid
> and i.tabid = c15.tabid
> and i.tabid = c16.tabid
> and part1 = c1.colno
> and part2 = c2.colno
> and part3 = c3.colno
> and part4 = c4.colno
> and part5 = c5.colno
> and part6 = c6.colno
> and part7 = c7.colno
> and part8 = c8.colno
> and part9 = c9.colno
> and part10 = c10.colno
> and part11 = c11.colno
> and part12 = c12.colno
> and part13 = c13.colno
> and part14 = c14.colno
> and part15 = c15.colno
> and part16 = c16.colno
> order by tabname, idxname;>
--
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/_/ Mag. Peter Kolmhofer __ _ _____ _/
_/_/ Informix & DB2 DBA __ --/_|___\\______ _/
_/_/ Porsche Informatik (Austria) _ _ ( _ _ \\) _/
_/_/ A-5101 Bergheim, Handelszentrum 7 -(_)-------(_)- _/
_/_/ +43 662 4670-6258 fax: -6501 email:kop@porsche.co.at _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/