Subject: Schema Printing Help
From: [email-masked] (Lester Knutsen)
Date: 7 Feb 1997 18:53:25 -0500
Newsgroups: comp.databases.informix

> I need to print  the schema for my entire database.  The format I need
> is:  
> <Stuff deleted>
> I think this should be easy to do, but have not had success.  If you
> have an answer or can tell me where to find one please e-mail me at
> [email-masked]

This is an old shell/awk script I have used to do this.

Regards - Lester
<------------------------cut here------------------------------------------->
#############################################################################
# PROGRAM: 	dbinfo - Database_Information, will display the results of
#		running the informix "info" command for columns, indexes, 
#		privileges and status for all tables in an informax database
#
# USAGE:	dbinfo database_name 
#
# AUTHOR:  	Lester B. Knutsen
#		Advanced DataTools Corporation
#		703-256-0267
#               [email-masked]
# 		Copyright (c) 1993, Advanced DataTools Corporation
# 		All rights reserved.
#############################################################################
usage() {
echo "usage: $1 database" ; exit 1
}
[ $# != 1 ] && { usage $0
}
TMPFILE=/tmp/dbi.$$; LOGFILE=/tmp/dblog.$$; LINES_PER_PAGE=64
dbaccess $1 - 2>$LOGFILE <<EOF
	output to "$TMPFILE" without headings select tabname from systables
	where tabid > 99 and tabname not in 
		("sysmenus","sysmenuitems","syscolatt","syscolval")
	order by tabname; update statistics;
EOF
[ ! -f $TMPFILE ] && { cat $LOGFILE; exit 1; }
{ echo "DATABASE $1 $LINES_PER_PAGE"
for table in `cat $TMPFILE`
do
echo "TABLE $table"
dbaccess $1 - 2>/dev/null <<EOF
	info columns for $table;    info indexes for $table; 
	{ info privileges for $table; info status for $table; }
EOF
done
} | nawk '
BEGIN { lineno = 0; pageno = 1; footer = 3; "date +%m\/%d\/%y" | getline today;
}
function prheader() {
	if ( pageno > 1 ) printf("%c",12)
	printf("\n\nDate: %s %55sPage: %4d\n",today,"",pageno)
	printf("%30sDatabase: %s\n","",database)
	printf("________________________________________")
	printf("________________________________________\n\n")
	lineno = 6; pageno++
}
function newtable() {
	table = $2; if ( lineno > 6 ) prheader()
	printf("%30sTable: %s\n","",table)
	printf("________________________________________")
	printf("________________________________________\n")
	lineno = lineno + 2
}
function colheader() {
	if ( lineno >= ( lines_per_page - 6 - footer  ) ) prheader()
	printf("\n\nColumns for %s\n",table)
	printf("________________________________________\n\n")
	printf("Column name          Type                                    Nulls\n")
	printf("-----------          ----                                    -----\n")
	lineno = lineno + 6
}
function idxheader() {
	if ( lineno >= ( lines_per_page - 6 - footer ) ) prheader()
	printf("\n\nIndexes for %s\n",table)
	printf("________________________________________\n\n")
	printf("Index name	    Owner     Type    Cluster  Columns\n")
	printf("----------	    -----     ----    -------  -------\n")
	lineno = lineno + 6
}
function prvheader() {
	if ( lineno >= ( lines_per_page - 6 - footer ) ) prheader()
	printf("\n\nPrivileges for %s\n",table)
	printf("________________________________________\n\n")
	printf("User	     Select             Update             Insert  Delete  Index  Alter\n")
	printf("----	     ------             ------             ------  ------  -----  -----\n")
	lineno = lineno + 6
}
function statheader() {
	if ( lineno >= ( lines_per_page - 5 - footer ) ) prheader()
	printf("\n\nStatus for %s\n",table)
	printf("________________________________________\n\n")
	lineno = lineno + 5
}
NR==1 	{ database=$2; lines_per_page = $3; prheader() }
NR > 1 && $0 > "" { 
	if ( lineno >= ( lines_per_page - footer )) prheader()
	if ( $1 == "TABLE" ) newtable()
	else if ( $1 == "Column" ) colheader()
	else if ( $1 == "Index" ) idxheader()
	else if ( $1 == "User" ) prvheader()
	else if ( $1 == "Table" ) statheader()
	else print $0 
	lineno++
} '; rm $TMPFILE $LOGFILE

#############################################################################
#  Lester Knutsen                               [email-masked]     #
#  Advanced DataTools Corporation               Voice: 703-256-0267         #
#  Grant group privileges for Informix databases with DB Privileges         #
#  Visit our Web page: http://www.access.digex.net/~lester                  #
#  Washington Area Informix User Group: http://www.access.digex.net/~waiug  #
#############################################################################