Re: [Q] how to find how many row in each table in database?

Larry Leung (llarry_at_hkstar.com) wrote:
: hello everyone,
:
: i just wonder is there a better way to find out how many row of each
: table in
: the database. It is because i am assigned a job to refresh our
: development
: database from the production one.
:
: by 'select count(*)' from each table seems time consuming.
:
: thanks in advance
: larry

Larry,

What you should do is make a SQL script that creates another SQL script containing all tables. Try the following:

SET HEAD OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SPOOL SCRIPT.SQL
SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||';' FROM ALL_TABLES
SPOOL OFF At this point, there will be a script called "SCRIPT.SQL" that contains all "SELECT COUNT(*)" statements you will need.

To find the results of this script, do the following:

SET ECHO ON
@SCRIPT.SQL -Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 90+ Oracle tips, visit my Web Page:                       <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

I see you work at the Hong Kong Star. Did you know that the McDonalds at the Star Ferry Terminal is the second most visited (behind Moscow's)? Received on Tue Jul 29 1997 - 00:00:00 CDT