www .Just SQL. com  

Script Name :

Description :

a site for the SQL*Plus specialist

Site Map


‘Describes’ a table, and counts total number of rows.

-- 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:
 '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
set echo on
-- end

[Home] [Inspiration] [About this Site] [Scripts Intro] [Publications]

e-Mail Connection