Re: SMI query for Index column names

On Thu, 24 Sep 1998, Art S. Kagel wrote: > Douglas Agnew wrote: > > the solution would appear to be a union across all the part1--part16 > > where the value is not zero. > [SNIP] > > > 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. > > Actually you are querying the database's system catalog tables not SMI, > but I won't nit pick ;-) I just want to add that if any column is > declared as descending in the index (less common in 7.3x since 7.3 can > use ascending indexes for descending searches) the column number in the > partxx will be negated so the filter should be: c.colno = abs(x.part1) > etc. Given a tabid as pre-requisite information, this query produces a set of column names in a single row of data for each index for an OnLine table (max 16 parts). The simplication to SE with 8 parts is trivial. Note that this loses the information about whether the columns are ascending or descending. SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, c01.colname col01, c02.colname col02, c03.colname col03, c04.colname col04, c05.colname col05, c06.colname col06, c07.colname col07, c08.colname col08, c09.colname col09, c10.colname col10, c11.colname col11, c12.colname col12, c13.colname col13, c14.colname col14, c15.colname col15, c16.colname col16 FROM "informix".SysIndexes t0, "informix".SysColumns c01, OUTER "informix".SysColumns c02, OUTER "informix".SysColumns c03, OUTER "informix".SysColumns c04, OUTER "informix".SysColumns c05, OUTER "informix".SysColumns c06, OUTER "informix".SysColumns c07, OUTER "informix".SysColumns c08, OUTER "informix".SysColumns c09, OUTER "informix".SysColumns c10, OUTER "informix".SysColumns c11, OUTER "informix".SysColumns c12, OUTER "informix".SysColumns c13, OUTER "informix".SysColumns c14, OUTER "informix".SysColumns c15, OUTER "informix".SysColumns c16 WHERE t0.tabid = <<tabid>> AND ABS(t0.part1) = c01.colno AND t0.tabid = c01.tabid AND ABS(t0.part2) = c02.colno AND t0.tabid = c02.tabid AND ABS(t0.part3) = c03.colno AND t0.tabid = c03.tabid AND ABS(t0.part4) = c04.colno AND t0.tabid = c04.tabid AND ABS(t0.part5) = c05.colno AND t0.tabid = c05.tabid AND ABS(t0.part6) = c06.colno AND t0.tabid = c06.tabid AND ABS(t0.part7) = c07.colno AND t0.tabid = c07.tabid AND ABS(t0.part8) = c08.colno AND t0.tabid = c08.tabid AND ABS(t0.part9) = c09.colno AND t0.tabid = c09.tabid AND ABS(t0.part10) = c10.colno AND t0.tabid = c10.tabid AND ABS(t0.part11) = c11.colno AND t0.tabid = c11.tabid AND ABS(t0.part12) = c12.colno AND t0.tabid = c12.tabid AND ABS(t0.part13) = c13.colno AND t0.tabid = c13.tabid AND ABS(t0.part14) = c14.colno AND t0.tabid = c14.tabid AND ABS(t0.part15) = c15.colno AND t0.tabid = c15.tabid AND ABS(t0.part16) = c16.colno AND t0.tabid = c16.tabid; If you want to list these as a series of rows then you use a 16-way UNION as suggested. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn