Re: SQL EXPERTS ONLY

From: Paul Zola <pzola_at_us.oracle.com>
Date: 1996/07/30
Message-ID: <4tm23u$3ip_at_inet-nntp-gw-1.us.oracle.com>#1/1

In <4tip79$q5u_at_newsbf02.news.aol.com> mgreen1962_at_aol.com (MGreen1962) writes:

} 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

Here's an example that prints out all of the information for the 'SCOTT' owner. Modify to taste.

REM ###############################################

REM
REM
set heading off
set termout off
set verify off
set feedback off
set pagesize 0
column info word_wrapped

spool tmp.sql

select 'select ''Table Name                      Row Count'' from dual;'
        from dual;
select 'select ' || '''' || rpad(owner || '.' || table_name,30)
        || '''' || ',  count(*) from ' || owner ||'.'
        || table_name || ';'  as info

from all_tables
where owner = 'SCOTT';

spool off

set termout on
set heading on
start tmp.sql
exit

REM ###############################################

==============================================================================
Paul Zola            Technical Specialist         World-Wide Technical Support
------------------------------------------------------------------------------

GCS H--- s:++ g++ au+ !a w+ v++ C+++ UAV++$ UUOC+++$ UHS++++$ P+>++ E-- N++ n+

    W+(++)$ M+ V- po- Y+ !5 !j R- G? !tv b++(+++) !D B-- e++ u** h f-->+ r*

---

Disclaimer: 	Opinions and statements are mine, and do not necessarily
		reflect the opinions of Oracle Corporation.

Received on Tue Jul 30 1996 - 00:00:00 CEST