Index question

Hi, I need know what SMI tables are involved in indexes. With sysindexes I can know Ids and Tabid of each index, but, which tables are necessary to know index columns names? Dbaccess shows this information, but I need to do a query .... Thanks Paola

Just syscolumns, sysindexes and systables systables to get the tabname syscolumns to get the colname, joined to systables on tabid sysindexes to get index name, part1 joined to syscolumns on colno, part2 joined on an alias for syscolumns on colno, part3 joined to an alias for syscolumns on colno ... and systables on tabid to get tabname. The part1, part2, part3 resolves the issue with composite indexes. here is a simple query to get the first 1-3 parts of an index select tabname, p1.colname, p2.colname, p3.colname,idxname from systables t, sysindexes i, syscolumns p1, outer syscolumns p2, outer syscolumns p3 where t.tabid > 99 and t.tabid = i.tabid and p1.tabid = i.tabid and p1.colno = part1 and p2.tabid = i.tabid and p2.colno = part2 and p3.tabid = i.tabid and p3.colno = part3 -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of pamadeo@cespi.unlp.edu.ar Sent: Monday, July 12, 2004 2:43 PM To: ids@iiug.org Subject: Index question [3229] Hi, I need know what SMI tables are involved in indexes. With sysindexes I can know Ids and Tabid of each index, but, which tables are necessary to know index columns names? Dbaccess shows this information, but I need to do a query .... Thanks Paola

Thank you very much. Is just what I need... Paola Mensaje citado por "KOLAYA, SCOTT M" <SCOTT_M_KOLAYA@fleet.com>: > Just syscolumns, sysindexes and systables > > systables to get the tabname > syscolumns to get the colname, joined to systables on tabid > sysindexes to get index name, part1 joined to syscolumns on colno, part2 > joined on an alias for syscolumns on colno, part3 joined to an alias for > syscolumns on colno ... and systables on tabid to get tabname. > > The part1, part2, part3 resolves the issue with composite indexes. > > here is a simple query to get the first 1-3 parts of an index > > select tabname, > p1.colname, > p2.colname, > p3.colname,> idxname > from systables t, > sysindexes i, > syscolumns p1, > outer syscolumns p2, > outer syscolumns p3 > where t.tabid > 99 > and t.tabid = i.tabid > and p1.tabid = i.tabid > and p1.colno = part1 > and p2.tabid = i.tabid > and p2.colno = part2 > and p3.tabid = i.tabid > and p3.colno = part3 > > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On > Behalf Of pamadeo@cespi.unlp.edu.ar > Sent: Monday, July 12, 2004 2:43 PM > To: ids@iiug.org > Subject: Index question [3229] > > > > Hi, I need know what SMI tables are involved in indexes. With sysindexes I > can > know Ids and Tabid of each index, but, which tables are necessary to know > index > columns names? > Dbaccess shows this information, but I need to do a query .... > > Thanks > Paola > > >