How to read database table definition from Unix Shell?

  A UNIX developer or an admin gets to refer to a database table to check for a particular field type, or to check for the columns of a table. Instead of connecting to sqlplus manually every time to get the column names or to connect to toad, we can write a simple shell script which will read or get the table definition. We will see in this article how to create a simple script for this requirement and use it like a command. 

Let us write a script in a file named 'getTable':

#!/usr/bin/ksh

if [ $1 = "-c" ]; then
   cols=1
   table=$2
else
   cols=0
   table=$1
fi

USER="scott"
PASSWD="tiger"
INST="school"

x=`sqlplus -s $USER/$PASSWD@$INST  <<EOF
set heading off
spool result.txt
desc $table
spool off
EOF`

head -1 result.txt | grep -q ERROR

if [ $? -eq 0 ]; then
   echo Invalid table $table
   exit
fi

if [ $cols -eq 1 ]; then
  awk 'NR>2{print $1}' result.txt
else
  awk 'NR>2{printf "%-30s  %-15s\n", $1, !($4)?$2:$4;}' result.txt
fi

\rm result.txt

How to run the script:

1. To get the columns and field type's, say, for a table 'employee', the script 'getTable' can be run as shown below.(In order to know, how to run a script like a command, refer this.)

#getTable employee
EMP_ID         NUMBER(3)
EMP_NAME       VARCHAR2(20)
EMP_DOB        DATE

2. In order to get only the columns of the employee table:

#getTable -c employee
EMP_ID
EMP_NAME
EMP_DOB  

3.  If one is looking for the definition of a particular field, you can do a 'grep' on the output as well.

#getTable employee | grep NAME
EMP_NAME         VARCHAR2(20)

4.  If the table name is invalid, an appropriate error message will be provided:

#getTable employees
Invalid table employees

The script is self explanatory. However, for beginners, the script does the following:

1.  Establishes a connection to sqlplus.

2.  Gets the definition of the table.

3.  If table not defined, errors and quits.

4.  If -c option specified, displays only the column names, else displays the columns along with their field types.
5.  The first 2 lines contain the header information and hence discarded.
6.  Keep in mind, the desc command output contains 3 columns, column name, NOT NULL field and field type.

Happy Shell Scripting!!!


Note: The example taken here is of Oracle database.

Tweet