I am trying to write some SQL to retrieve index information from sysindices so that I can re-create the schema of a table. Normal indexes are OK, I can just use the sysindexes view, and link to the syscolumns table, which will give me the columns that the index is on.

The problem comes with functional indexes. I appreciate that the procid exists in the indexkeys columns, but how can I extract this ??? is there an equivalent to ikeyextractcolno for the procid ???

Thanks in advance

Jason

Hi,

You can extract the complete schema of the table using dbschema which will include the indexes on the table also.

dbschema -d db_name -t table_name

I hope it helps.

Yes, I appreciate you can do this with DBSchema, but I have written an app that goes throught all stored procs, views, and tabls, and extracts them to individual files in relevant directories (e.g. c:\DBSchema\Tables c:\DBSChema\Views etc). It all works fine for all exept functional indexes.

Thanks Anyway

Jason

I am want to try informix. but I do not have the informix sortware. can you send me that software,please ?

hi
if you are still interested, this might be an answer:

select ikeyextractcolno(indexkeys,<nr>) from sysindices
where nr is a contor from 1 to ncols

Hi Jason,

to extract the id of the function you could select
indexkeyarray_out(indexkeys) as allparts from sysindices
Then you will get something like "-3 [1], <526>(5) [1], 4 [1]".

The "<526>" is the ID of the function (procid in sysprocedures).

The "[1]" seems to be the operator class (opclassid in sysopclasses)
which you only have to specify in your create statement when its not the default value
(e.g. btree_ops for default access method btree).

The "-3", "5" and "4" are the column numbers (colno in syscolumns),
when less than zero then in DESC order.

So this Index would be something like "(colno3 DESC, ud_function(colno5), colno4)".

If you have one or more user defined functions in your index,
all 16 parts in sysindexes are 0.

I just checked out these things by trying. So no guarantee.
When somebody has exact information, please let me know.