Re: Get all child table and key names of a parent table
My query is reasonably fast. So you are welcome to try it. I wrap it
with a shell script below to pretty up the output:
#!/usr/bin/ksh
trap "cleanup ; exit" 0 1 2 15
function usage {
printf "\\n\\n Usage: %s <database> [table] \\n\\n" "`basename $0`" 1>&2
exit 1
}
function errorMsg {
printf "\\n\\n ERROR: %s \\n\\n" "$eMsg" 1>&2
usage
}
function cleanup {
rm -f /tmp/scons_$$.out
}
if [ $# -lt 1 -o $# -gt 2 ] ; then
usage
fi
if [ $# -eq 2 ] ; then
whereComp="and ( pt.tabname = \\"$2\\" or dt.tabname = \\"$2\\" )"
else
whereComp=""
fi
if [ `sdb | grep $1 | wc -l` -eq 0 ] ; then
eMsg="Database: $1 not Found"
errorMsg
fi
dbaccess -e $1 <<EOF 1>/dev/null 2>/dev/nullset isolation to dirty read;
unload to /tmp/scons_$$.outselect
pt.tabname, dt.tabname,
pc1.colname,
pc2.colname,
pc3.colname,
pc4.colname,
pc5.colname,
pc6.colname,
pc7.colname,
pc8.colname,
pc9.colname,
pc10.colname,
pc11.colname,
pc12.colname,
pc13.colname,
pc14.colname,
pc15.colname,
pc16.colname,
dc1.colname,
dc2.colname,
dc3.colname,
dc4.colname,
dc5.colname,
dc6.colname,
dc7.colname,
dc8.colname,
dc9.colname,
dc10.colname,
dc11.colname,
dc12.colname,
dc13.colname,
dc14.colname,
dc15.colname,
dc16.colname,
case drefs.delrule
when 'C' then 'D'
else drefs.delrule
end
from
sysconstraints dcons, sysconstraints pcons,
systables pt, systables dt,
outer syscolumns pc1, outer syscolumns pc2, outer syscolumns pc3,
outer syscolumns pc4,
outer syscolumns pc5, outer syscolumns pc6, outer syscolumns pc7,
outer syscolumns pc8,
outer syscolumns pc9, outer syscolumns pc10, outer syscolumns pc11,
outer syscolumns pc12,
outer syscolumns pc13, outer syscolumns pc14, outer syscolumns pc15,
outer syscolumns pc16,
outer syscolumns dc1, outer syscolumns dc2, outer syscolumns dc3,
outer syscolumns dc4,
outer syscolumns dc5, outer syscolumns dc6, outer syscolumns dc7,
outer syscolumns dc8,
outer syscolumns dc9, outer syscolumns dc10, outer syscolumns dc11,
outer syscolumns dc12,
outer syscolumns dc13, outer syscolumns dc14, outer syscolumns dc15,
outer syscolumns dc16,
sysindexes pidx, sysindexes didx,
sysreferences drefs
where
dcons.constrtype = "R" and
dcons.idxname = didx.idxname and
dcons.constrid = drefs.constrid and
pcons.constrid = drefs.primary and
pcons.idxname = pidx.idxname and
drefs.ptabid = pt.tabid and -- primary table and fields
pt.tabid = pc1.tabid and pt.tabid = pc2.tabid and pt.tabid =
pc3.tabid and pt.tabid = pc4.tabid and
pt.tabid = pc5.tabid and pt.tabid = pc6.tabid and pt.tabid =
pc7.tabid and pt.tabid = pc8.tabid and
pt.tabid = pc9.tabid and pt.tabid = pc10.tabid and pt.tabid =
pc11.tabid and pt.tabid = pc12.tabid and
pt.tabid = pc13.tabid and pt.tabid = pc14.tabid and pt.tabid =
pc15.tabid and pt.tabid = pc16.tabid and
pidx.part1 = pc1.colno and pidx.part2 = pc2.colno and pidx.part3 =
pc3.colno and pidx.part4 = pc4.colno and
pidx.part5 = pc5.colno and pidx.part6 = pc6.colno and pidx.part7 =
pc7.colno and pidx.part8 = pc8.colno and
pidx.part9 = pc9.colno and pidx.part10 = pc10.colno and pidx.part11 =
pc11.colno and pidx.part12 = pc12.colno and
pidx.part13 = pc13.colno and pidx.part14 = pc14.colno and pidx.part15
= pc15.colno and pidx.part16 = pc16.colno and
dcons.tabid = dt.tabid and -- dependent table and fields
dt.tabid = dc1.tabid and dt.tabid = dc2.tabid and dt.tabid =
dc3.tabid and dt.tabid = dc4.tabid and
dt.tabid = dc5.tabid and dt.tabid = dc6.tabid and dt.tabid =
dc7.tabid and dt.tabid = dc8.tabid and
dt.tabid = dc9.tabid and dt.tabid = dc10.tabid and dt.tabid =
dc11.tabid and dt.tabid = dc12.tabid and
dt.tabid = dc13.tabid and dt.tabid = dc14.tabid and dt.tabid =
dc15.tabid and dt.tabid = dc16.tabid and
didx.part1 = dc1.colno and didx.part2 = dc2.colno and didx.part3 =
dc3.colno and didx.part4 = dc4.colno and
didx.part5 = dc5.colno and didx.part6 = dc6.colno and didx.part7 =
dc7.colno and didx.part8 = dc8.colno and
didx.part9 = dc9.colno and didx.part10 = dc10.colno and didx.part11 =
dc11.colno and didx.part12 = dc12.colno and
didx.part13 = dc13.colno and didx.part14 = dc14.colno and didx.part15
= dc15.colno and didx.part16 = dc16.colno
$whereComp
order by
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,
19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
;
EOF
nawk -F"|" '{
printf("%s: ", $1 )
for(i=3;i<=18;++i) {
if ($i == "") {
break
}
printf("%s ", $i)
}
printf(" <--[%s] %s: ", $(NF - 1), $2 )
for(i=19;i<= NF - 2 ; ++i) {
if ($i == "") {
break
}
printf("%s ", $i)
}
printf("\\n")
}' /tmp/scons_$$.out