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