Indexes via DBI driver in Perl (DBD::Informix)

Indexes via DBI driver in Perl (DBD::Informix)

Post by Jonathan Leffle » Sun, 31 Dec 1899 09:00:00

>  how can I get the indexes of some or all tables in a database using
>  DBI driver? I'm using the Informix-DBD, and there's no hint about this
>  topic.

...mainly because it isn't something that DBI supports directly.

Your best bet is probably to go to the IIUG software archives
(hmm, how many times have I said this today?
and get hold of SQLCMD.  In the code for the INFO statements
(, you will find the statements I use to generate the
information loosely equivalent to "INFO INDEXES FOR sometable",
which is not a built-in command in SQL so you cannot run it
directly from DBD::Informix.  Note that the statements assume
you know the table number (systables.tabid) entry for the table.
To deal with lists of tables, you will have to revise the code


Guardian of DBD::Informix v1.00.PC1 --
     "I don't suffer from insanity; I enjoy every minute of it!"


1. Indexes via DBI driver in Perl

Just perform this SQL:

SELECT idxname
FROM sysindexes si, systables st
WHERE si.tabid - st.tabid
  AND st.tabname = "mytable";

If you also want the column definitions that is more difficult and ugly but
looks something very much like this:

SELECT idxname, c1.colname, c2.colname, c3.colname, ......
FROM sysindexes si,
        systables st,
        syscolumns c1,
        OUTER syscolumns c2,
        OUTER syscolumns c16
WHERE si.tabid - st.tabid
  AND st.tabname = "mytable"
  AND c1.tabid = st.tabid AND c1.colno = ABS(si.part1)
  AND c2.tabid = st.tabid AND c2.colno = ABS(si.part2)
  AND c3.tabid = st.tabid AND c3.colno = ABS(si.part3)
  AND c16.tabid - st.tabid AND c16.colno = ABS(si.part16)

Art S. Kagel

2. Permission to VIEW bot not the TABLE

3. Perl DBI-0.93 DBD-Informix-0.58 on SCO

4. Help on changing control:

5. perl DBD, perl DBI programming

6. info from one file to another

7. Perl & DBI/DBD::Pg confusion with finish

8. Indexes with Progress

9. Where are Perl DBI and DBD for PostgreSQL??

10. Perl, DBD, DBI, HPUX 9.05


12. Perl 5.003, DBI, DBD and Oracle problem