how to retreive info about indexes through system tables?

hello all here is one way thru dbaccess: echo "info indexes for <table>;"|dbaccess <db name> but how can this be done thru a sql editor hitting the system tables? thanks tom

Perhaps joining the sysindexes and systables tables on tabid ?? -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of tomcaml@gmail.com Sent: Wednesday, November 12, 2008 11:31 AM To: informix-list@iiug.org Subject: how to retreive info about indexes through system tables? hello all here is one way thru dbaccess: echo "info indexes for <table>;"|dbaccess <db name> but how can this be done thru a sql editor hitting the system tables? thanks tom _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Tom, Join systables to sysindexes by tabid and join sysindexes to syscolumns by the absolute value of the "part*" columns to syscolumns.colno. Each contains the colno of one of the key columns in the index in order of appearance. You need to use ABS(part...) because if the column is in the index DESCENDING then its colno will be negated in sysindexes. This will not work for 'special' indexes like functional indexes. There you will have to parse the sysindices table that underlies the sysindexes VIEW (in 9.xx and later sysindexes is not a table but a view into sysindices). You can look at the code for my dbschema and dostats utilities to see how to do this for functional indexes. See the files dostat.ec or myschema.d/ print_indexes.ec in my package utils2_ak which you can download from the IIUG Software Repository. Feel free to borrow the print_indexes code for you own applications if you are not producing a commercial product for sale (then you'll have to negotiate a license with me - sorry). Art On Wed, Nov 12, 2008 at 12:31 PM, <tomcaml@gmail.com> wrote: > hello all > > > here is one way thru dbaccess: > echo "info indexes for <table>;"|dbaccess <db name> > > but how can this be done thru a sql editor hitting the system tables? > > > thanks > tom > > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.