Re: table description
proberts@informix.com (Paul Roberts) writes:
>In article <318paj$2ks@dns1.NMSU.Edu> tbowman@nmsu.edu (Todd Bowman) writes:
>>I am fairly new to Informix and have access only to the
>>informix documents, no 3rd party tutorial books.
>Welcome aboard. I liked it so much, I joined the company!
Just recently? Remember, the new guy buys!
>>My goal is, via an sql statemnt to grab a description of a
>>particular table, including column names, type and size of
>>data, indexes, not nulls, etc...
>>
>>Is there a system table that holds this info? If so can a user
>>get at it?
>Yes, and yes. Try "systables" and "syscolumns". I think it is
>all in there. Null-ness or not-null-ness is coded a little
>cryptically along with datatype. There is also "sysauth" or
>something like that lists who has which permissions to which
>tables.
System catalogs are documented in chapter 2 of INFORMIX GUIDE TO SQL
Reference (December 1991 is my copy, part number 000-7118).
Also, in case you didn't know, there's the dbschema utility
(documented just about everywhere; check the index) that will provide
robustly the database schema.
Below is a 4gl report I wrote awhile back to get me the schema for an
existing database, including most (all?) of the stuff you're asking
about. I think it still runs on the most recent versions, although the
system catalogs have expanded quite a bit recently. The cryptic null
indication Paul refers to is the 256 offset the code below tests.
=======================================================================
Dennis J. Pimple dennisp@informix.com Opinions expressed
Senior Consultant -------------------- are mine, and do not
Informix Software Inc Voice: 303-850-0210 necessarily reflect
Denver Colorado USA Fax: 303-779-4025 those of my employer.
## sch.4gl - run against a Unix 5.01 Database to get a schema listing
## tables, columns, and indexes in a format maybe a little nicer than
## dbschema.
DEFINE r_systab RECORD
tabname CHAR(18),
owner CHAR(8),
dirpath CHAR(64), # site || dbname for OnLine
tabid INTEGER,
rowsize SMALLINT,
ncols SMALLINT,
nindexes SMALLINT,
nrows INTEGER,
created DATE,
version INTEGER,
tabtype CHAR(1)
END RECORD
DEFINE r_syscol RECORD
colname CHAR(18),
colno SMALLINT,
coltype SMALLINT,
collength SMALLINT
END RECORD
DEFINE r_sysind RECORD
idxname CHAR(18),
idxtype CHAR(1),
clustered CHAR(1)
END RECORD
DEFINE r_db CHAR(60)
DEFINE r_file CHAR(60)
DEFINE online SMALLINT # TRUE if running online
MAIN
DEFINE ans CHAR(1)
DEFINE sqltxt CHAR(1000)
DEFINE sqlexec CHAR(80)
CLEAR SCREEN
INITIALIZE r_db TO NULL
WHILE r_db IS NULL
PROMPT "Enter Database (full path or in DBPATH): " FOR r_db
END WHILE
DATABASE r_db
INITIALIZE ans TO NULL
WHILE ans IS NULL
PROMPT "Update Statistics (Y/N)? " FOR CHAR ans
END WHILE
IF ans MATCHES "[Yy]" THEN
MESSAGE "Updating ..."
UPDATE STATISTICS
MESSAGE ""
END IF
INITIALIZE r_file TO NULL
WHILE r_file IS NULL
PROMPT "Enter file name to report schemas to: " FOR r_file
IF int_flag THEN
LET int_flag=FALSE
EXIT PROGRAM
END IF
END WHILE
# see if we're running online
LET sqlexec = fgl_getenv("SQLEXEC")
IF sqlexec IS NULL OR sqlexec MATCHES "*sqlturbo" THEN
LET online = TRUE
LET sqltxt = "SELECT systables.tabname, systables.owner, ",
"systables.site||systables.dbname, systables.tabid, ",
"systables.rowsize, systables.ncols, ",
"systables.nindexes, systables.nrows, ",
"systables.created, systables.version, ",
"systables.tabtype,"
ELSE
LET online = FALSE
LET sqltxt = "SELECT systables.tabname, systables.owner, ",
"systables.dirpath, systables.tabid, ",
"systables.rowsize, systables.ncols, ",
"systables.nindexes, systables.nrows, ",
"systables.created, systables.version, ",
"systables.tabtype,"
END IF
LET sqltxt = sqltxt CLIPPED, " syscolumns.colname, ",
"syscolumns.colno, syscolumns.coltype, ",
"syscolumns.collength ",
"FROM systables, syscolumns ",
"WHERE systables.tabid > 99 ",
"AND syscolumns.tabid = systables.tabid ",
"ORDER BY systables.tabname, syscolumns.colno"
PREPARE s_systab FROM sqltxt
DECLARE c_systab CURSOR FOR s_systab
START REPORT rpt_sch TO r_file
FOREACH c_systab INTO r_systab.*, r_syscol.*
MESSAGE "Outputting Table ", r_systab.tabname CLIPPED,
" Column ", r_syscol.colname CLIPPED, " "
OUTPUT TO REPORT rpt_sch()
END FOREACH
ERROR ""
MESSAGE "File ", r_file CLIPPED, " holds schemas"
FINISH REPORT rpt_sch
END MAIN
REPORT rpt_sch()
DEFINE type_text CHAR(41)
DEFINE sel_text CHAR(200)
DEFINE idx_text CHAR(18)
DEFINE ict, jct, kct, tct, rct SMALLINT
OUTPUT
LEFT MARGIN 0
FORMAT
FIRST PAGE HEADER
LET rct=0
PRINT "DATABASE ", r_db CLIPPED, COLUMN 72, TODAY USING "mm/dd/yy"
SKIP 3 LINES
PAGE HEADER
PRINT COLUMN 74, "Page ", PAGENO USING "<<<<<"
IF r_syscol.colno>1 AND r_systab.ncols>r_syscol.colno THEN
IF r_systab.tabtype = "T" THEN
PRINT "TABLE ";
ELSE
IF r_systab.tabtype = "V" THEN
PRINT "VIEW ";
ELSE
PRINT "SYNONYM ";
END IF
END IF
PRINT r_systab.tabname CLIPPED, " (cont)"
PRINT "COLUMN ",
"TYPE INDEX"
PRINT "------------------ ",
"----------------------------------------- ------------------"
ELSE
SKIP 3 LINES
END IF
ON EVERY ROW
IF r_syscol.colno=1 THEN
LET rct=rct+1
IF r_systab.ncols<51 THEN
NEED r_systab.ncols+5 LINES
ELSE
SKIP TO TOP OF PAGE
END IF
SKIP 2 LINES
IF r_systab.tabtype = "T" THEN
PRINT "TABLE ";
ELSE
IF r_systab.tabtype = "V" THEN
PRINT "VIEW ";
ELSE
PRINT "SYNONYM ";
END IF
END IF
PRINT r_systab.tabname CLIPPED, " ",
r_systab.nrows USING "<<<<