Re: counting rows in all_tables ???????

From: Steve Gundersen <swg_at_nt.com>
Date: 1997/07/24
Message-ID: <33D7DA4D.1E9F@nt.com>#1/1

Erik Oosterling wrote:
>
> Hello,
>
> i got a smaal problem but don't know how to start.
>
> P.
>
> I want to make a query which count all the rows in each table that
> ALL_TABLES returns.

>...............snip
> ERIK OOSTERLING
This comes from our friends at Oracle on their web site...

REM SQL Script:

-- Generating a report of all tables with their record counts
-- Code File Name : count.sql
-- Temporary File Name : tmp_count.sql (created when count.sql is

executed)
-- Report File Name : count.lst
-- When this script is run, it creates a report ( file name count.lst) in the
-- current directory which lists out the table names and their record counts.

-- The output is echoed to the screen as well.
-- It creates a temporary file tmp_count.sql in the current directory.
-- Change the table name USER_TABLES to ALL_TABLES or DBA_TABLES to

change
-- the scope of the query.
set echo off
set time off timing off
set head off
set pagesize 0
set linesize 200
set feedback off
set termout off
!rm tmp_count.sql
spool tmp_count.sql

select 'set head off' from dual;
select 'set pagesize 0' from dual;
select 'set linesize 80' from dual;
select 'set feedback off' from dual;
select 'spool count.lst' from dual;
select 'select '||'''List of Tables with their Record Counts as of

'||to_char(sysdate,'DD-MON-YY HH24:MI:SS')||''''||'from dual;' from dual;
select 'select '||'''Total Records in '||rpad(table_name,30,' ')||'::'||''''||', count(*) from '||table_name||';' from all_tables
/
select 'spool off' from dual;
spool off
set termout on
start tmp_count;

enjoy.......... Received on Thu Jul 24 1997 - 00:00:00 CDT