## Monday, July 5, 2010

### 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
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.