#!/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