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?

Jonathan Leffler · Accepted Answer · 2011-09-09 23:04:59Z

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).

Collectives™ on Stack Overflow

Find Informix table and column details using SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related