Find Informix table and column details using SQL query

Question

I want to get Informix database table information and column information such as

• table names
• column names of the table
• data types of the columns
• data type length (ex: if column is varchar)
• constraints on the columns

I am able to find the table names and column names using this query to get the table names:

select tabname from systables 

to find the column name

SELECT TRIM(c.colname)   AS table_dot_column 
FROM "informix".systables AS t, "informix".syscolumns AS c 
WHERE t.tabname = 'agent_status'   
AND t.tabtype = 'T'
and t.tabid = c.tabid 
AND t.tabid >= 100  ; 

but I am not able to find the data types and constraints on the columns.

Can anyone tell me the SQL query to get the total details of the table mentioned above?

Answer 1

Wow! That's a complex query - best treated as at least two, probably three queries; or maybe that's what you had in mind anyway.

You might want to select tabid and owner in the first query, and it is good form to use "informix".systables rather than just systables (though that only really matters in a MODE ANSI database, but then it really does matter).

The query on syscolumns is fine, though the t.tabid >= 100 clause is probably redundant, unless you definitively want to prevent people learning about the columns in system catalog tables. Also, it can be helpful to know about the columns in a view, so the tabtype = 'T' might be too stringent.

Decoding the data types is fiddly. For the built-in types, it is not too difficult; for user defined types, it is considerably harder work. The coltype and collength (and extended_d) tell you about the type. You can find C code to translate the basic types in my SQLCMD package, in sqltypes.ec. You can find some simple SQL (that may not be complete) in $INFORMIXDIR/etc/xpg4_is.sql.

Constraint information is stored in sysconstraints and related tables. You can find code for some constraints in the SQLCMD source already mentioned (file sqlinfo.ec).