How to create shadow table in Informix

Your query will work, but it might be more idiomatic to use a join:

SELECT *  FROM "informix".systables AS t  JOIN "informix".syscolumns AS c ON t.tabid = c.tabid WHERE t.tabname = 'table_a';

Also, be aware that the system catalog will only contain table_A in mixed case if you created the table while you had DELIMIDENT set in the environment and you created the table with the name enclosed in double quotes. Normally, the table name will be in all lower-case letters in the system catalog; similarly for column names.

However, that's all tangential to your question. Dealing with user-defined types in all their glory is painful. However, if you're dealing with normal databases, you can certainly work like that, though it might be easier to use DB-Schema (dbschema) to generate the schema for a table, and then trap that. You could actually do that through a stored procedure using the SYSTEM statement, but I'd probably do it from outside a stored procedure. It does depend on what else you need to do. The pre-image and post-image for each field could be modestly costly.

If you have IBM Informix Dynamic Server 11.70, you can create your CREATE {audit} TABLE statement dynamically, and then execute the statement. So, you'd build up your query using a FOREACH loop in the stored procedure to add each column in turn, and then execute the statement to create the audit table. You'll have to decode the type, too. You could/should use a procedure for that, too. I'm assuming that tabname is a variable passed to the stored procedure, and c_colno, c_colname, and c_typename are local variables (as is cts, short for 'create table statement', and pad):

LET cts = 'CREATE TABLE ' || tabname || '(';LET pad = '';FOREACH SELECT c.colno, c.colname, type_name(c.coltype, c.collength)          INTO c_colno, c_colname, c_typename          FROM "informix".systables AS t          JOIN "informix".syscolumns AS c            ON t.tabid = c.tabid         WHERE t.tabname = tabname         ORDER BY c.colno    LET cts = cts || pad || 'pre_'  || c_colname || ' ' || c_coltype;    LET cts = cts || ',' || 'post_' || c_colname || ' ' || c_coltype;    LET pad = ',';END FOREACH;LET cts = cts || ');';

You may want to deal with NOT NULL and primary key constraints and all sorts of other things, but this gives you the basics to be going on with.

In addition to JonathanLeffler's answer, there is the type_name procedure:

    CREATE PROCEDURE type_name(coltype INTEGER, colsize INTEGER)            RETURNING VARCHAR(128);            DEFINE toRet                    VARCHAR(128);            DEFINE size_5                   VARCHAR(5);            DEFINE decimal_p, decimal_s     INTEGER;            DEFINE decimal_t                VARCHAR(16);            DEFINE varchar_m, varchar_n     INTEGER;            DEFINE varchar_t                VARCHAR(16);            LET size_5 = '(' || TRIM(CAST(colsize AS CHAR(5))) || ')';             -- Precision            LET decimal_p = TRUNC(colsize/256);            -- Scale            LET decimal_s = colsize - 256 * decimal_p;            -- Decimal total            LET decimal_t = '(' || TRIM(CAST(decimal_p as VARCHAR(8))) || ',' || TRIM(CAST(decimal_s as VARCHAR(8))) || ')';            -- VARCHAR(M,N)            LET varchar_n = decimal_p;            LET varchar_m = decimal_s;            LET varchar_t = '(' || TRIM(CAST(varchar_m as VARCHAR(8))) || ',' || TRIM(CAST(varchar_n as VARCHAR(8))) || ')';            SELECT                CASE coltype                    WHEN 0   THEN 'char' || size_5                     WHEN 1   THEN 'smallint'                    WHEN 2   THEN 'integer'                    WHEN 3   THEN 'float'                    WHEN 4   THEN 'smallfloat'                    WHEN 5   THEN 'decimal' || decimal_t                    WHEN 6   THEN 'serial'                    WHEN 7   THEN 'date'                    WHEN 8   THEN 'money' || decimal_t                    WHEN 9   THEN 'null'                    WHEN 10  THEN 'DATETIME YEAR TO FRACTION(3)'                    WHEN 11  THEN 'byte'                    WHEN 12  THEN 'TEXT'                    WHEN 13  THEN 'VARCHAR' || varchar_t                    WHEN 14  THEN 'INTERVAL'                    WHEN 15  THEN 'NCHAR' || size_5                    WHEN 16  THEN 'NVARCHAR' || varchar_t                    WHEN 17  THEN 'INT8'                    WHEN 18  THEN 'SERIAL8'                    WHEN 19  THEN 'SET'                    WHEN 20  THEN 'MULTISET'                    WHEN 21  THEN 'LIST'                    WHEN 22  THEN 'ROW'                    WHEN 23  THEN 'COLLECTION'                    WHEN 24  THEN 'ROWDEF'                    WHEN 40  THEN 'LVARCHAR'  || size_5                     WHEN 256 THEN 'CHAR' || size_5 || ' NOT NULL'                    WHEN 257 THEN 'SMALLINT NOT NULL'                    WHEN 258 THEN 'INTEGER NOT NULL'                    WHEN 259 THEN 'FLOAT NOT NULL'                    WHEN 260 THEN 'SMALLFLOAT NOT NULL'                    WHEN 261 THEN 'DECIMAL' || decimal_t || ' NOT NULL'                    WHEN 262 THEN 'SERIAL NOT NULL'                    WHEN 263 THEN 'DATE NOT NULL'                    WHEN 264 THEN 'MONEY' || decimal_t || ' NOT NULL'                    WHEN 265 THEN 'null NOT NULL'                    WHEN 266 THEN 'DATETIME YEAR TO FRACTION(3) NOT NULL'                    WHEN 267 THEN 'BYTE NOT NULL'                    WHEN 268 THEN 'TEXT NOT NULL'                    WHEN 269 THEN 'VARCHAR' || varchar_t || ' NOT NULL'                    WHEN 270 THEN 'INTERVAL NOT NULL'                    WHEN 271 THEN 'nchar(' || size_5 || ') NOT NULL'                    WHEN 272 THEN 'nvarchar' || varchar_t || ' NOT NULL'                    WHEN 273 THEN 'int8 NOT NULL'                    WHEN 274 THEN 'serial8 NOT NULL'                    WHEN 275 THEN 'set NOT NULL'                    WHEN 276 THEN 'multiset NOT NULL'                    WHEN 277 THEN 'list NOT NULL'                    WHEN 278 THEN 'row NOT NULL'                    WHEN 279 THEN 'collection NOT NULL'                    WHEN 280 THEN 'rowdef NOT NULL'                    WHEN 296 THEN 'LVARCHAR'  || varchar_t || ' NOT NULL'                    ELSE 'ERROR'                END datatype            INTO toRet            FROM systables            WHERE tabid = 1;            IF toRet = 'ERROR' THEN               RAISE EXCEPTION -746, 0, 'Unknow datatype ' || coltype;            END IF            RETURN toRet;    END PROCEDURE;

Credit to this.