how can I query table index column in informix database

Question

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

Simon Riddle · Accepted Answer · 2022-08-31 07:31:58Z

2

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.

answered Aug 31, 2022 at 7:31

Simon Riddle

1,1415 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Leffler Over a year ago

Note that if the index is descending, the column numbers in sysindexes (partN) will be negative. Using ABS(part) works for those columns too.

Collectives™ on Stack Overflow

how can I query table index column in informix database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related