table or index – Informix Archive

rkusenet — Tue, 18 May 2004 15:01:13 -0400 — source: Usenet: comp.databases.informix

this is the structure of the table systabnames in the database sysmaster |------------------------------------------|-------------------|----------|---| |Column Name |Column Type |Null |Key| |------------------------------------------|-------------------|----------|---| |partnum |INTEGER |null |UNQ| |dbsname |CHAR(128) |null | | |owner |CHAR(32) |null | | |tabname |CHAR(128) |null | | |collate |CHAR(32) |null | | |------------------------------------------|-------------------|----------|---| What I do is take the tabname and do a lookup in the database (using dbsname) systables/sysindexes to find out whether it is a table or index. Not a efficient way. is there a table in sysmaster which can tell whether the tabname is an index or a table.

↪ replying to rkusenet

Jonathan Leffler — Wed, 19 May 2004 05:56:25 GMT — source: Usenet: comp.databases.informix

rkusenet wrote: > this is the structure of the table systabnames in the database sysmaster > > |------------------------------------------|-------------------|----------|---| > |Column Name |Column Type |Null |Key| > |------------------------------------------|-------------------|----------|---| > |partnum |INTEGER |null |UNQ| > |dbsname |CHAR(128) |null | | > |owner |CHAR(32) |null | | > |tabname |CHAR(128) |null | | > |collate |CHAR(32) |null | | > |------------------------------------------|-------------------|----------|---| > > What I do is take the tabname and do a lookup in the database (using dbsname) > systables/sysindexes to find out whether it is a table or index. Not a efficient > way. is there a table in sysmaster which can tell whether the tabname > is an index or a table. Well, systables in the database contains the table names, and sysindices (sysindexes is a view) contains the index names. Unless you are searching only within a single database, the dbsname part makes it a bit difficult to write generic SQL. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

↪ replying to rkusenet

malcolm — 19 May 2004 01:13:54 -0700 — source: Usenet: comp.databases.informix

You can use ti_nkeys in systabinfo. If this is zero it seems to be a real table and 1 indicates an index. regards Malcolm "rkusenet" <rkusenet@sympatico.ca> wrote in message news:<2gv4qiF71mi9U1@uni-berlin.de>... > this is the structure of the table systabnames in the database sysmaster > > |------------------------------------------|-------------------|----------|---| > |Column Name |Column Type |Null |Key| > |------------------------------------------|-------------------|----------|---| > |partnum |INTEGER |null |UNQ| > |dbsname |CHAR(128) |null | | > |owner |CHAR(32) |null | | > |tabname |CHAR(128) |null | | > |collate |CHAR(32) |null | | > |------------------------------------------|-------------------|----------|---| > > What I do is take the tabname and do a lookup in the database (using dbsname) > systables/sysindexes to find out whether it is a table or index. Not a efficient > way. is there a table in sysmaster which can tell whether the tabname > is an index or a table.

↪ replying to malcolm

rkusenet — Wed, 19 May 2004 11:01:42 -0400 — source: Usenet: comp.databases.informix

"malcolm" <malcolm.weallans@btopenworld.com> wrote in message news:3efc1745.0405190013.6148978c@posting.google.com... > You can use ti_nkeys in systabinfo. If this is zero it seems to be a > real table and 1 indicates an index. > > regards Yup it works. Thanks a lot. > "rkusenet" <rkusenet@sympatico.ca> wrote in message news:<2gv4qiF71mi9U1@uni-berlin.de>... > > this is the structure of the table systabnames in the database sysmaster > > > > |------------------------------------------|-------------------|----------|---| > > |Column Name |Column Type |Null |Key| > > |------------------------------------------|-------------------|----------|---| > > |partnum |INTEGER |null |UNQ| > > |dbsname |CHAR(128) |null | | > > |owner |CHAR(32) |null | | > > |tabname |CHAR(128) |null | | > > |collate |CHAR(32) |null | | > > |------------------------------------------|-------------------|----------|---| > > > > What I do is take the tabname and do a lookup in the database (using dbsname) > > systables/sysindexes to find out whether it is a table or index. Not a efficient > > way. is there a table in sysmaster which can tell whether the tabname > > is an index or a table.