how can I query table index column in informix database-CodePudding

I use informix DB, I hope I can query table index column
Now ,I just can query table index
I dont know how to query index column....
this is my query

SELECT indexname,f.tabid  FROM sysfragments f, sysindexes i, systables t WHERE  f.indexname = i.idxname AND i.tabid = t.tabid  and t.tabname ='mytable'

it just can show indexname can not show indexname's column...
how can I do? thanks

CodePudding user response：

The part1 to part16 columns of the sysindexes table records the column number for each part of the index. This can then be matched against the syscolumns table as shown in this example:

SELECT indexname, f.tabid, sc1.colname, sc2.colname, sc3.colname
  FROM sysfragments f, sysindexes i, systables t,
   syscolumns sc1, OUTER syscolumns sc2, OUTER syscolumns sc3
  WHERE  f.indexname = i.idxname AND i.tabid = t.tabid
    AND sc1.tabid = t.tabid AND sc1.colno = i.part1
    AND sc2.tabid = t.tabid AND sc2.colno = i.part2
    AND sc3.tabid = t.tabid AND sc3.colno = i.part3
    AND t.tabname = 'table';

For composite indexes having more than 3 parts the SQL would have to be extended with outer joins on additional syscolumns tables.