RE: Information on Primary Keys, Indexes
Jignesh,
> Hi,
>
> How to get information on all primary keys, unique keys, indexes?
>
> Please advise.
>
> Regards
> Jignesh
The script below was written by Curtis Crowson and might give you some
of the information you need. Curtis, please let me know if this script
is not for public consumption.
Mike Badar
ESRI-Denver
1 International Court
Broomfield, CO 80021-3200
303-449-7779
mbadar@esri.com
www.esri.com
> Hi,
>
> How to get information on all primary keys, unique keys, indexes?
>
> Please advise.
>
> Regards
> Jignesh
#!/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