Re: Schema Printing Help
Don,
> 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
> donj@lca.com
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
# lester@access.digex.net
# 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 lester@access.digex.net #
# 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 #
#############################################################################