--
-- DESCRIBE.SQL
--
-- Peter Robson
-- December 2000
--
-- This script Describes, and then Counts total rows in all
-- tables found in the Oracle dd table 'user_tables' where the
-- table_name begins with 'M%'
--
-- The script could be modified against any 'where' clause; it could
-- also be modified to read from 'all_tables'. The 'owner' for
-- each table_name would then have to be defined.
--
-- This is a 2-Level query, requiring only 1 spool file (a.sql).
-- The outer Level-2 query is driven (in this case) by the
-- where clause on user_tables.
--
-- There are 2 inner, Level-1 queries, one to build the familiar
-- 'describe' output, the second to count the number of rows
-- in each table being described.
--
-- There is no limit to the number of other Level-1 queries
-- which could be wrapped into this outer, Level-2 script.
--
-- Note in particular the way 'set echo' and 'set termout' are
-- used to suppress all but the required output.
--
set feedback off
set pagesize 0
set echo off
set termout off
--
spool a.sql
--
-- Opening Level-2 verb:
--
select
'set heading on'|| chr(10) ||
'ttitle left ''Details of table: '''||table_name||' skip 2' || chr(10) ||
--
-- First Level-1 script:
--
'select column_name
,decode(nullable,''N'',''NOT NULL'',''Y'','' '','' '') "Null?"
,data_type "Type"
from user_tab_columns
--
-- '||table_name||' is here a component of the Level-2 script
--
where table_name='||''''||table_name||'''' || chr(10)||
'order by column_id;' || chr(10)||
'ttitle off' || chr(10)||
'set echo off' || chr(10)||
--
-- Second Level-1 script:
--
'select count(*) "No of Rows in '||table_name||'" from '||table_name||';'||chr(10)
--
-- Closing Level-2 line:
--
from user_tables where table_name like 'M%'
;
spool off
--
--
set feedback 2
set pagesize 9999
set termout on
@a.sql
--
set echo on
--
-- end
-- |
