使用 Informix 系统目录(1)

#!/bin/ksh
###########################################################
#
# findcol database column
#
# returns any table or column within 'database' that has some
# portion of 'column' within it's name
#
# Jack Parker 2002
#
###########################################################
echo "
select tabname[1,25], colname[1,25], coltype, collength
  from systables a, syscolumns b
 where a.tabid = b.tabid
   and (colname matches \\"*$2*\\" or tabname matches \\"*$2*\\");
" | dbaccess $1 - 2>/dev/null | tail +4l | grep -v ^$ | awk '
BEGIN {
dtp[0]="char"
dtp[1]="smallint"
dtp[2]="integer"
dtp[3]="float"
dtp[4]="smallfloat"
dtp[5]="decimal"
dtp[6]="serial"
dtp[7]="date"
dtp[8]="money"
dtp[9]="unknown"
dtp[10]="datetime"
dtp[11]="byte"
dtp[12]="text"
dtp[13]="varchar"
dtp[14]="interval"
dtp[15]="nchar"
dtp[16]="nvarchar"
dtp[17]="unk"
dtp[18]="unk"
dtp[19]="unk"
dtp[20]="unk"

datp[1] = "year"
int_start[1]=1
int_end[1]=5
datp[3] = "month"
int_start[3]=5
int_end[3]=7
datp[5] = "day"
int_start[5]=7
int_end[5]=9
datp[7] = "hour"
int_start[7]=9
int_end[7]=11
datp[9] = "minute"
int_start[9]=11
int_end[9]=13
datp[11] = "second"
int_start[11]=13
int_end[11]=15
datp[12] = "fraction(1)"
int_start[12]=15
int_end[12]=16
datp[13] = "fraction(2)"
int_start[13]=16
int_end[13]=17
datp[14] = "fraction(3)"
int_start[14]=17
int_end[14]=18
datp[15] = "fraction(4)"
int_start[15]=18
int_end[15]=19
datp[16] = "fraction(5)"
int_start[16]=19
int_end[16]=20
}
function fixnm(coll,tp)
{
   i = int(coll / 256)
   j = coll % 256
   if (tp == 0) {
if (j > i ) strg=i
else strg = sprintf("%s,%s",i,j)
}
   else {
if (i == 0) strg=j
else strg = sprintf("%s,%s",j,i)
}
   return strg
}
function fixdt(coll)
{
   i = coll % 16 + 1
   j = int((coll % 256) / 16 ) + 1
   k = int(coll / 256)
   ln = int_end[i] - int_start[j]
   ln = k - ln
   if (ln == 0 || j > 11) { strg = sprintf("%s to %s", datp[j], datp[i]) }
   else {
k int_end[j] - int_start[j]
k = k + ln
strg = sprintf("%s (%d) to %s", datp[j], k, datp[i])
}
   return strg
}
{
# Tab, colname, type, length
outstrg=""
tabname=$1
colname=$2
nonull=$3/256
coltype=$3%256
collength=$4
outstrg=dtp[coltype]
if (coltype == 0) {outstrg=sprintf("%s(%s)",outstrg, collength)}
if (coltype == 5 || coltype == 8 ) {
outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,0)) }
if (coltype == 13 ) {outstrg=sprintf("%s(%s)",outstrg,fixnm(collength,1)) }
if (coltype == 10 || coltype == 14 ) {
outstrg=sprintf("%s(%s)",outstrg,fixdt(collength)) }
printf("%-20s %-20s %-25s\\n", tabname, colname, outstrg)
}' 

#!/bin/ksh
###############################################################
#
# Quick script thrown together to find dependencies on 
# a table.
#
# Usage:
#  depend.sh database table
#
# This will list all tables which refer to this table either 
# by view or by foreign key.  It will also list any tables 
# that this table depends on.  It will recurse through a 
# dependency chain.
#
# Jack Parker 2003
#
###############################################################

find_par_view()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(b.tabname)
from sysdepend c, systables a, systables b
where btabid=a.tabid
  and dtabid=b.tabid
  and b.tabname = "$2";
EOF
}

find_child_view()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(b.tabname)
from sysdepend c, systables a, systables b
where btabid=a.tabid
  and dtabid=b.tabid
  and a.tabname = "$2";
EOF
}

find_par_fk()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(d.tabname) || ' ' || trim(constrname) || ' ' || state
from systables a, sysconstraints b, sysreferences c, systables d, 
  sysobjstate e
where constrtype='R'
  and a.tabid=b.tabid
  and b.constrid=c.constrid
  and b.constrname=e.name
  and c.ptabid=d.tabid
  and a.tabname = "$2";
EOF
}

find_child_fk()
{
dbaccess $1 - 2>/dev/null <<EOF
output to pipe "cat" without headings
select trim(d.tabname) || ' ' || trim(constrname) || ' ' || state
from systables a, sysconstraints b, sysreferences c, systables d, 
sysobjstate e
where constrtype='R'
  and a.tabid=b.tabid
  and b.constrid=c.constrid
  and b.constrname=e.name
  and c.ptabid=d.tabid
  and d.tabname = "$2";
EOF
}

rec_pfk()
{
idnt="$idnt-"
find_par_fk $1 $2 | grep -v "^$" |
while read junk
do
        echo $idnt "Parent of FK: $junk"
        tab=`echo $junk | cut -d" " -f1 `
        rec_pfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}

rec_cfk()
{
idnt="$idnt-"
find_child_fk $1 $2 | grep -v "^$" |
while read junk
do
        echo $idnt "Child of FK: $junk"
        tab=`echo $junk | cut -d" " -f1 `
        rec_cfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}

rec_pvw()
{
idnt="$idnt-"
find_par_view $1 $2 | grep -v "^$" |
while read tab
do
        echo $idnt "Parent View : $tab"
        rec_cfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}

rec_cvw()
{
idnt="$idnt-"
find_child_view $1 $2 | grep -v "^$" |
while read tab
do
        echo $idnt "Child of View : $tab"
        rec_cfk $1 $tab
done
idnt=`echo $idnt | awk '{a=length($0)-1; print substr($0,1,a)}'`
}

echo "

Database: $1
"
idnt=""
echo "Searching for tables that ($2) has an FK to:"
rec_pfk $1 $2
idnt=""
echo "Searching for tables that refer to ($2) with an FK:"
rec_cfk $1 $2
idnt=""
echo "Searching for Parent tables for potential view ($2):"
rec_pvw $1 $2
idnt=""
echo "Searching for Child tables for table ($2):"
rec_cvw $1 $2