Informix equivalent to mysql's SHOW CREATE TAB

问题:

Is there any equivalent to MySQL's SHOW CREATE TABLE <tablename> in informix? I would like to run it in the SQL Manager on the Server Studio and would also like to obtain information about a tables columns and their types. Is this possible? I've found the systables, syscolumns and information schema, but wasn't lucky with the results I got with the output of select * from... .

回答1:

I don't know such SQL command. From command line you can use dbschema utility:

C:\Informix>dbschema -t abc -d test
DBSCHEMA Schema Utility       INFORMIX-SQL Version 11.50.TC6DE

{ TABLE "informix".abc row size = 4 number of columns = 1 index size = 0 }
create table "informix".abc
  (
    xxx integer
  );

revoke all on "informix".abc from "public" as "informix";

If you want to read systables, then look at my script that dumps info about tables, columns, indexes etc: http://code.activestate.com/recipes/576621-dump-informix-schema-to-text/?in=user-186902 . In its source you will find systables queries you can use.

回答2:

You can also obtain the basic column names and types from within DB-Access or ISQL with

INFO COLUMNS FOR <table>

I don't know whether this works in SQL Manager or not, but it costs nothing to try.

回答3:

This SQL statement lists columns and types, but not defaults, etc.

select
  t.tabname, c.*
from
  systables t
join
  syscolumns c
on
  t.tabid = c.tabid
where
  t.tabname = 'table'