Querying database for columns

[mbyrom]

I'm currently working with an Informix database.
The database is quite large, quite old, and I have only rudimentary knowledge of the tables contained within the database.

I'd like to use some SQL that would check the database for column names

i.e. (search database for tables containing the column name "Line Code&quot

Is this possible?

 

[NatAnderson]

The following query will list the columns of a table named 'table':

SELECT sc.colname 
  FROM syscolumns sc, systables st 
 WHERE st.tabid=sc.tabid AND st.tabname='table'
ORDER BY sc.colno

There are more columns in the syscolumns table that define data type, column length, minimum value, and maximum value.

To search for a database containing the column "LineCode", the query would be:

SELECT st.tabname, sc.colname
  FROM syscolumns sc, systables st
 WHERE st.tabid=sc.tabid and sc.colname='LineCode'
ORDER BY 1

 

[mbyrom]

That's perfect, Thanks.

 

[ks1392]

Is there a lookup table for an English language description of the 'coltype' field in the syscolumns table?

TIA,
Kerr