Re: SQL EXPERTS ONLY

MGreen1962 wrote:
>
> I need to produce a report that shows how many records are in each table.
> i.e:
>
> Table_Name Number Recs
> ----------------------------------------------------------------------
> Employees 413
> Customer 5257
> ...
>
> How can I do this? Is there a datadictionary table that contains the
> number records in a table? Thanks to all that attempt to answer this
> question.
>
> Send suggestions to MGREEN1962_at_aol.com

You could run ANALYZE on all your tables, and then get the information from dba_tables.

	analyze table XXX compute statistics;
	analyze table YYY compute statistics;
	....
	select table_name, num_rows from dba_tables;

Running analyze on all of your tables can be a bit painful. Another alternative (also painful, but not as much) would be to build SQL to do a COUNT(*) on all of your tables and save that information into a temporary table.

Suppose you created a table to store row counts.

	create table count_table 
	( owner varchar2(30),
	  table_name varchar2(30), 
	  row_count number);

This script will create a script called temp1.sql which will hold insert statements that will put row counts for each table into count_table.

set pagesize 0
set termout off
set verify off
set feedback off
set echo off
spool temp1.sql
select 'insert into count_table select ''' owner || ',' || table_name || ''',count(*) from ' || owner || '.' || table_name || ';' from dba_tables;
spool off

The temp1.sql output file will contains lines like these.

insert to count_table select 'SYS','BOOTSTRAP$',count(*) from SYS.BOOTSTRAP$;
insert to count_table select 'SYS','TS$',count(*) from SYS.TS$;

After you run temp1.sql, you can then generate your report from count_table.

	select  owner, table_name, row_count
		from count_table
		order by table_name

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com