Checking columname for index
Hello, I am maintaining some SPL, which contains a CREATE INDEX statement. I am taking this opportunity to make the procedure a little more robust, by checking that the column in question is not already indexed before issuing the 'CREATE' The system catalogue does not seem to provide much help, as I can't link sysindexes with syscolumns. Does anyone have any SQL or ideas that may be able to help me in this? Thanks. Sent via Deja.com http://www.deja.com/ Before you buy.
Why can't you link syscolumns with sysindexes? It isn't very clean, but it can be done. In article <8bntn0$448$1@nnrp1.deja.com>, albertrocker@my-deja.com wrote: > Hello, > > I am maintaining some SPL, which contains a CREATE INDEX statement. > > I am taking this opportunity to make the procedure a little more > robust, by checking that the column in question is not already indexed > before issuing the 'CREATE' > > The system catalogue does not seem to provide much help, as I can't > link sysindexes with syscolumns. > > Does anyone have any SQL or ideas that may be able to help me in this? > > Thanks. > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
> Why can't you link syscolumns with sysindexes? It isn't very clean, but > it can be done Maybe I'm missing something, but I can't see how the 2 catalogue tables can be linked.. In a clean way or not! I apologise if this is a little basic - Can you give me any pointers as to what I should do to link the tables? Sent via Deja.com http://www.deja.com/ Before you buy.
The part# fields in sysindexes hold the colno field from syscolumns, in order that they appear in the index. So, using the combination of tabid and part# in sysindexes, you can get the column name from syscolumns. To get the first element in all indexes, you could do something like select col.colname from syscolumns col, sysindexes idx where col.colno = idx.part1 and col.tabid = idx.tabid; I just think it's wonderful that Informix didn't follow first normal form on the sysindexes table... In article <8bo2q0$9v9$1@nnrp1.deja.com>, albertrocker@my-deja.com wrote: > > > Why can't you link syscolumns with sysindexes? It isn't very clean, > but > > it can be done > > Maybe I'm missing something, but I can't see how the 2 catalogue tables > can be linked.. In a clean way or not! > > I apologise if this is a little basic - Can you give me any pointers as > to what I should do to link the tables? > > Sent via Deja.com http://www.deja.com/ > Before you buy. > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.
albertrocker@my-deja.com wrote: > Hello, > > I am maintaining some SPL, which contains a CREATE INDEX statement. > > I am taking this opportunity to make the procedure a little more > robust, by checking that the column in question is not already indexed > before issuing the 'CREATE' > > The system catalogue does not seem to provide much help, as I can't > link sysindexes with syscolumns. > > Does anyone have any SQL or ideas that may be able to help me in this? You could look at the code which implements "INFO INDEXES FOR tablename" in the SQLCMD program. It has the code you need. Pretty it isn't! As someone else commented, the SysIndexes table violates BCNF (it is in 1NF because RDBMS do not handle data that isn't in 1NF at all, but it is apalling 1NF). Note that *only* DB-Access and ISQL implement the INFO statement in the standard Informix product set. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>