Listing 1: find_fields.sh
#!/bin/sh
# ----------------------------------------------------
# FIND_FIELDS.SH October 18, 1994
# Scott A. Tarvainen Kyle V. Gleed
# ----------------------------------------------------
# This script queries selected Oracle databases to find
# related fields within different tables. The user is
# prompted to enter the database which is to be
# queried and the data field they wish to search for.
# The script queries the database and returns all
# tables within the database that contain the search
# field.
# ----------------------------------------------------
#
# Procedure to check for SQL connect errors
#
error_check() {
if [ -s /tmp/sqlerror.log ]; then
if ( grep -i error /tmp/sqlerror.log \
> /dev/null 2>&1 );then
clear
echo
echo -n "Following ERROR encountered "
echo "during database connection:"
echo
head -1 /tmp/sqlerror.log
echo
exit 1
fi
else
clear
echo
echo "$USERID/<PASSWORD>@$DATABASE is empty"
echo
exit 1
fi
}
#
# Process temporary working files
#
if [ -f /tmp/found.lst ]; then
rm /tmp/found.lst
fi
if [ -f /tmp/sqlerror.log ]; then
rm /tmp/sqlerror.log
fi
#
# Prompt user to enter database and
# data field information
#
clear
echo
echo -n "Enter SQLPLUS arguments as outlined below "
echo "when prompted"
echo ">> e.g. sqlplus USERID/PASSWORD@DATABASE <<"
echo
echo -n "Note: the PASSWORD field will not be "
echo "displayed for security reasons"
echo
echo -n "Enter USERID ------> "
read USERID
USERID=`echo $USERID | tr [a-z] [A-Z]`
echo -n "Enter PASSWORD ----> "
#
# Turn echo off so password won't be displayed
#
stty -echo
read PASSWORD
PASSWORD=`echo $PASSWORD | tr [a-z] [A-Z]`
#
# Turn echo back on
#
stty echo
echo
echo -n "Enter DATABASE ----> "
read DATABASE
DATABASE=`echo $DATABASE | tr [a-z] [A-Z]`
echo
#
# Prompt user to enter the query field to search for
#
echo -n "Enter QUERY FIELD -> "
read FIELD
FIELD=`echo $FIELD | tr [a-z] [A-Z]`
echo
#
# Query the database to build a list of tables
#
sqlplus -s $USERID/$PASSWORD@$DATABASE << xxENDxx \
> /tmp/sqlerror.log 2>&1
set pause off
set feedback off
set heading off
set pages 0
set lines 300
spool /tmp/tables
select TABLE_NAME
from USER_CATALOG;
spool off;
quit
xxENDxx
error_check
#
# Get description of each table in the
# database to match
#
for TABLE in `cat /tmp/tables.lst`
do
echo "Searching $TABLE"
sqlplus -s $USERID/$PASSWORD@$DATABASE << xxENDxx \
> /tmp/sqlerror.log 2>&1
set pause off
set feedback off
set heading off
set echo off
set pages 0
set lines 300
spool /tmp/struct
desc $TABLE;
spool off;
quit
xxENDxx
#
# Strip first two header lines off the file
#
ed -s /tmp/struct.lst << xxENDxx
1,2d
w
q
xxENDxx
#
# Attempt to match query field
#
for FIND_FIELD in `nawk '/^ /{print $1}' \
/tmp/struct.lst`
do
if [ "$FIND_FIELD" = "$FIELD" ]; then
echo $TABLE >> /tmp/found.lst
fi
done
done
echo
echo "Processing Finished."
sleep 1
echo
echo
#
# If query field was matched,
# print out matched table name(s)
#
if [ -s /tmp/found.lst ]; then
echo -n "Matches for $FIELD found in the "
echo "following database tables:"
echo
cat /tmp/found.lst
echo
#
# If query field not matched, inform the user
#
else
echo -n "No matches found for $FIELD "
echo "in the database tables."
echo
fi
|