Indexes via DBI driver in Perl
<!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\\