Re: SMI query for Index column names

Gabor Heppes wrote: > You don't have to be afraid to use OR in SQL (instead of unions and > outer joins) ... try: > > select x.idxname, c.colname > from syscolumns c, > systables t, > sysindexes x > where t.tabname = 'adm_translation' > and t.owner = 'DBO' > and x.tabid = t.tabid > and c.tabid = t.tabid > and ( c.colno = x.part1 or > c.colno = x.part2 or > c.colno = x.part3 or > c.colno = x.part4 or > c.colno = x.part5 or > c.colno = x.part6 or > c.colno = x.part7 or > c.colno = x.part8 or > c.colno = x.part9 or > c.colno = x.part10 or > c.colno = x.part11 or > c.colno = x.part12 or > c.colno = x.part13 or > c.colno = x.part14 or > c.colno = x.part15 or > c.colno = x.part16 )> > or the same with the ABS(x.part*) variety This query works as a way of listing the columns in each index. It corresponds to (a simplified version of) the UNION query, listing one row of data for each column in the index. What it does not do is preserve some information which is usually considered rather important, namely the order of the columns in the index. That is, if you get the output for a single index listing 3 columns, you cannot tell which permutation of the columns represents the order of the columns in the index. Thus, you cannot necessarily distinguish the indexes: CREATE INDEX ix1 ON Table1(Col1, Col2, Col3); CREATE INDEX ix2 ON Table2(Col3, Col1, Col2);Maybe this is careless index design, but there's no way to guarantee that the data for these two indexes will be printed in the order of their respective columns. To do that, you have to use one of the much more complex queries -- either the UNION or the OUTER version. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>