set linesize 130;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* script to spool the SQL to perform a count on    */
/*    all the tables within the database            */
/*                                                  */
/*         @c:\oracle\sql\bld_cnt1.sql              */ 
/*                                                  */
/* NOTE: will build all tables                      */
/*                                                  */
/* ************************************************ */

set termout off;
set echo off;
set feedback off;

col dummy 	noprint format a1;
col dummy2 	noprint format a1;
col table_name 	noprint format a1;
col command 	format a80;

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

spool bld_cnt1.rslt

select 'select '''||owner||''' as "OWNER", '''||table_name||''' as "TABLE", count(*) from '||owner||'.'||table_name||';' 
   from all_tables
where owner not in ('SYSTEM', 'SYS')
/* and table_name like 'PS%' */
order by owner, table_name;
                 
spool off;
set heading on;
set pagesize 100;
set termout on;

commit;










