Schema tables with a describe

Asked by a user if he could have a quick and dirty solution to viewing the table name with their descriptions for a schema he was working on, only a handful of tables.

He already had the table names but would have been easy enough to spool them from sqlplus.
Probably a million and one ways to do this but came up with a quick shell script to give him what he wanted.


I only post this on here as you need to put an exit before the sqlplus - sure someone will correct me. If I get a chance will have a play around - took the shell specifier out as I wanted to try different shells.

Remember to use the silent, head off and feedback off to suppress the output.

array=(
table1
table2
table3
…etc
)

for i in "${array[@]}"
do
exit | sqlplus -silent user/passwd@database << EOF
set head off
set feedback off
select 'select ''${i}'' from dual;' from dual;
select 'desc '|| '${i}' ||' ' from dual;
EOF
done

from the output…..

TABLE
------
table1

Name Null? Type
----------------------------------------- -------- ----------------------------
ANOTHER_COL VARCHAR2(10)
ANOTHER_COL2 NUMBER


etc....

slight variation to do direct counts :

cat count.sh
array=(
OP_PARTITION
INTER_NCS_BATCH_CONTROL
INTER_NCS_QUEUE
INTER_NCS_QUEUE_BATCHES
INTER_NCS_QUEUE_SEQ
INTER_NCS_ROUTING
INTER_QUEUE_CONTROLLER
INTER_QUEUE_SETTINGS
NEXTFARE_INTER_NCS
RAW_SNMP_AFC_EVENT
)
for i in "${array[@]}"
do
exit | sqlplus -silent / as sysdba << EOF
set head off
set feedback off
SET LINESIZE 200
SET TRIMSPOOL ON
SET TRIMOUT ON
SET WRAP OFF
SET TERMOUT OFF
SET PAGESIZE 0
select '${i}' from dual;
select count(*) from NEXTFARE_MAIN.${i};
EOF
done



** IF you are using Windows similar could be achieved using Powershell

$sampleArray =
"EDIT_INCIDENT",
"EDIT_LOC",
"TEST3"

for ($i=1; $i -lt $sampleArray.Length; $i++) {
$sqlQuery = "select 'desc $($sampleArray[$i]);' from dual;"
$sqlQuery | sqlplus -silent sys/sys_pass@EDIT-01 as sysdba
}