Look in the sysmaster database: systables, syscolumns, etc.

Look at www.informix.com for manuals.

There should be no problem using crystal with informix.


J.

-----Original Message-----
From: "mehl@cyvest.com" <mehl@cyvest.com>
To: informix-list@iiug.org
Date: 7 Aug 2006 13:48:24 -0700
Subject: Columns in each table

Hello --

I am a newbie to Informix, and will start a contract tomorrow to write
Crystal Reports on an Informix db.

Can anyone suggest SQL code to obtain the following information from an
Informix database?:
Table, Column, DataType, Length, Description (?)

I use the following code in MS SQL Server
SELECT
SUBSTRING(table_name,1,20) AS "Table",
SUBSTRING(column_name,1,30) AS "Column",
SUBSTRING(data_type,1,13) AS "DataType",
ISNULL(character_maximum_length, ' ') AS "Length",
STR(numeric_precision) AS "Precision",
numeric_scale AS "Scale",
CONVERT(nvarchar(150), ISNULL(s.value, ' ')) AS Description
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s. TABLE_NAME),
'IsMsShipped')=0 AND
table_schema = 'DBO' AND
table_name NOT LIKE 'sys%' AND
table_name NOT LIKE 'dtprop%'
ORDER BY table_name, ordinal_position

Can you also tell me in what Informix component to enter such SQL code?
In SQL Server, for example, I could enter it in Query Analyzer or
create a query in the Management Console.

Thanks for any help.

Larry Mehl

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list


Jean Sagi
jeansagi@myrealbox.com
jeansagi@gmail.com