Markus Neifer wrote:
Hi, i've tried to query the dictionary for indexes on a table as
follows:
SELECT KEYS.COLNAME, KEYS.COLNO, KEYS.COLSEQ, KEYS.ORDERING
FROM SYSIBM.SYSKEYS KEYS, SYSIBM.SYSINDEXES IDXS
WHERE IDXS.NAME = KEYS.IXNAME
AND IDXS.CREATOR = KEYS.IXCREATOR
AND IDXS.TBNAME = 'FOOBAZ'
AND IDXS.TBCREATOR = 'FOOBAR'
;
Looks like i missed something because this gives me lots of doubled
lines. Any hints?
TIA, Markus
Hi!
I'm not so familiar with SYSKEYS, but I think this is because some keys
are used in several indexes in the same TBNAME / TBCREATOR aren't them?
This is how I tried it:
SELECT KEYS.COLNAME
, KEYS.COLNO
, KEYS.COLSEQ
, KEYS.ORDERING
, IDXS.TBNAME
, IDXS.TBCREATOR
, KEYS.*
, IDXS.*
FROM SYSIBM.SYSKEYS KEYS
, SYSIBM.SYSINDEXES IDXS
WHERE IDXS.NAME = KEYS.IXNAME
AND IDXS.CREATOR = KEYS.IXCREATOR
AND IDXS.TBNAME = 'FOOBAZ'
AND IDXS.TBCREATOR = 'FOOBAR'
ORDER BY KEYS.COLNAME
, KEYS.COLNO
, KEYS.COLSEQ
, KEYS.ORDERING
, IDXS.TBNAME
, IDXS.TBCREATOR
;
