SQL Server sp_help alternative in Informix Server

Posted in 2007

Summary

A user asked for an Informix equivalent of SQL Server's sp_help to inspect table structure. Replies suggested the dbschema utility (dbschema -d database -t table), the INFO commands in dbaccess or sqlcmd (INFO TABLES / INFO COLUMNS FOR TABLE / INFO INDEXES FOR TABLE), and querying the system catalog tables (systables, syscolumns, etc.). The poster wrote a syscolumns/systables query mapping coltype numbers to type names; Art Kagel corrected it, noting coltype 0 is CHAR not NCHAR and that you must use MOD(coltype,256) because 256 is added for NOT NULL columns. Resolved.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Stored Procedures & SPL

VENKATESH — Tue, 09 Oct 2007 06:55:24 — source: IIUG Forums & Mailing Lists

Hi All, I want to query the structure of the table inside the Informix Dynamic Server. I know that in MS SQL Server the stored procedure to do this is sp_help followed the table name. Can anybody let us know what is the similar query or routine in IDS. Thanks, Venkatesh

↪ replying to VENKATESH

Amitava — Tue, 09 Oct 2007 07:22:16 — source: IIUG Forums & Mailing Lists

dbschema -d database -t table from command prompt Thanks.. Amitava "VENKATESH BHUPATHI" <bhupav@amvescap. To com> ids@iiug.org Sent by: cc ids-bounces@iiug. org Subject SQL Server sp_help alternative in Informix Server [10069] 09/10/2007 12:25 Please respond to ids@iiug.org Hi All, I want to query the structure of the table inside the Informix Dynamic Server. I know that in MS SQL Server the stored procedure to do this is sp_help followed the table name. Can anybody let us know what is the similar query or routine in IDS. Thanks, Venkatesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to VENKATESH

Martin — Tue, 09 Oct 2007 08:09:04 — source: IIUG Forums & Mailing Lists

Hi, not knowing SQL Server nor sp_help, I think in Informix you want to query the system catalog. This is a set of tables/views (in IDS 11.10 there are 65) that exist within every Informix database. To name a few: systables, syscolumns, sysviews, sysindexes, ... You can read about the system catalog in the manual "IBM Informix Guide to SQL: Reference", Chapter 1 "System Catalog Tables" (in this manual for IDS 11.10). Or you can "explore" the tables of any database using dbaccess. Start with something like "select tabname from systables where tabid < 100;" to get a list of the tables that comprise the system catalog ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management IBM Deutschland GmbH Chairman of the Supervisory Board: Hans Ulrich Märki Board of Management: Martin Jetter (Chairman), Rudolf Bauer, Christian Diedrich, Christoph Grandpierre, Matthias Hartmann, Thomas Fell, Michael Diemer Corporate Seat: Stuttgart, Germany; Reg.-Gericht: Amtsgericht Stuttgart, HRB-Nr.: 14 562 WEEE-Reg.-Nr. DE 99369940 ids-bounces@iiug.org wrote on 09.10.2007 08:55:25: > Hi All, > > I want to query the structure of the table inside the Informix Dynamic Server. > I know that in MS SQL Server the stored procedure to do this is sp_help > followed the table name. > > Can anybody let us know what is the similar query or routine in IDS. > > Thanks, > Venkatesh > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Martin

VENKATESH — Tue, 09 Oct 2007 11:48:40 — source: IIUG Forums & Mailing Lists

Hi Martin, Your suggestion helped me to go in the right direction. Following is the query that I am using to know the columns and their datatypes, SELECT syscolumns.colname,CASE WHEN coltype = 0 THEN 'NCHAR' WHEN coltype = 1 THEN 'SMALLINT' WHEN coltype = 8 THEN 'MONEY' WHEN coltype = 2 THEN 'INTEGER' WHEN coltype = 11 THEN 'BYTE' WHEN coltype = 3 THEN 'FLOAT' WHEN coltype = 12 THEN 'TEXT' WHEN coltype = 4 THEN 'SMALLFLOAT' WHEN coltype = 13 THEN 'NVARCHAR' WHEN coltype = 5 THEN 'DECIMAL' WHEN coltype = 14 THEN 'INTERVAL' WHEN coltype = 6 THEN 'SERIAL' WHEN coltype = 15 THEN 'NCHAR' WHEN coltype = 7 THEN 'DATE' WHEN coltype = 16 THEN 'NVARCHAR' ELSE 'unknown' END AS DataTYpe FROM syscolumns, systables WHERE syscolumns.tabid = systables.tabid AND systables.tabname = 'tablename' For those who may not have console and working just on SDK might help. Thanks, Venkatesh

↪ replying to VENKATESH

ART KAGEL, — Tue, 09 Oct 2007 13:02:10 — source: IIUG Forums & Mailing Lists

You should be using 'MOD(coltype, 256) = 0' in the case statement (and perhaps use 'MOD(coltype, 256) AS coltype' in the projection list as well) since IDS adds 256 to the coltype if the column is declared NOT NULL. Also, coltype == 0 is CHAR not NCHAR. Art S. Kagel ----- Original Message ----- From: Venkatesh Bhupathi <ids@iiug.org> To: ids@iiug.org At: 10/09 7:49:06 Hi Martin, Your suggestion helped me to go in the right direction. Following is the query that I am using to know the columns and their datatypes, SELECT syscolumns.colname,CASE WHEN coltype = 0 THEN 'NCHAR' WHEN coltype = 1 THEN 'SMALLINT' WHEN coltype = 8 THEN 'MONEY' WHEN coltype = 2 THEN 'INTEGER' WHEN coltype = 11 THEN 'BYTE' WHEN coltype = 3 THEN 'FLOAT' WHEN coltype = 12 THEN 'TEXT' WHEN coltype = 4 THEN 'SMALLFLOAT' WHEN coltype = 13 THEN 'NVARCHAR' WHEN coltype = 5 THEN 'DECIMAL' WHEN coltype = 14 THEN 'INTERVAL' WHEN coltype = 6 THEN 'SERIAL' WHEN coltype = 15 THEN 'NCHAR' WHEN coltype = 7 THEN 'DATE' WHEN coltype = 16 THEN 'NVARCHAR' ELSE 'unknown' END AS DataTYpe FROM syscolumns, systables WHERE syscolumns.tabid = systables.tabid AND systables.tabname = 'tablename' For those who may not have console and working just on SDK might help. Thanks, Venkatesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to VENKATESH

ART KAGEL, — Tue, 09 Oct 2007 13:53:13 — source: IIUG Forums & Mailing Lists

As suggested, you can use the Informix dbschema utility: dbschema -d <database> -t <table> or you can query the system catalog. If you are using the Informix dbaccess or Jonathan Leffler's sqlcmd query tools you can use the built-in INFO command: INFO TABLES; INFO COLUMNS FOR TABLE tablename; INFO INDEXES FOR TABLE tablename; Art S. Kagel ----- Original Message ----- From: Venkatesh Bhupathi <ids@iiug.org> To: ids@iiug.org At: 10/09 2:55:48 Hi All, I want to query the structure of the table inside the Informix Dynamic Server. I know that in MS SQL Server the stored procedure to do this is sp_help followed the table name. Can anybody let us know what is the similar query or routine in IDS. Thanks, Venkatesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.