Indexes via DBI driver in Perl
Ralf Heydenreich asked how to list table indexes through Perl DBI with the Informix DBD. Jonathan Leffler said DBI has no direct support and pointed to the SQLCMD source's INFO INDEXES queries. Art S. Kagel gave a select joining sysindexes and systables on tabname, plus a longer one using outer joins on syscolumns for column names. Jonathan Stowe noted the question was cross-posted. Answered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> Hi, <br> how can I get the indexes of some or all tables in a database using DBI <br> driver? I'm using the Informix-DBD, and there's no hint about this <br> topic. <p>Ralf.</html>
Ralf Heydenreich wrote: > 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? http://www.iiug.org) and get hold of SQLCMD. In the code for the INFO statements (sqlinfo.ec), 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 slightly. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"
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
Ralf Heydenreich wrote:
>
> Hi,
> 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.
>
> Ralf.
On Tue, 22 Aug 2000 17:33:42 -0400, Art S. Kagel Wrote:
> 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)
> ;
>
I might point out that this guy posted the same question in
comp.lang.perl.misc and I told him the tables to examine but it appears that
he hasnt worked out how to post to more than one newsgroup at a time except
in a serial fashion.
/J\\