Re: How to count total number of rows from all user-tables

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<u86n9.37759$dp1.97044_at_rwcrnsc52.ops.asp.att.net>...
> Why not have SQL create SQL? I too am too lazy to type 100 or more commands
> to get this info.
> spool mycommands.sql
> select 'select table_name,count(*) from table_name;' from user_tables order
> by table_name;
> spool off
> @mycommands.sql

> It is possible to create some pl/sql stored procedure with dynamic sql that
> would issue the 100 statements. It could put the info into a table and then
> report on that. But the use sql to write sql is a tried and true method.
> Jim
> "shinwar" <jshinwar_at_yahoo.com> wrote in message
> news:77e5857.0210031319.47f28818_at_posting.google.com...
> > Hi, Oracle Gurus
> >
> >
> > Before this I have posted one question. Now, I have one more question
> > on how to count total number rows of each user table using a single
> > query statement. I really do not know if it can be done in a single
> > SQL statement. Let's say. I have 100 user tables. I do not want to
> > issue 100 SQL commands for this job.
> >
> > Instead, using a single SQL which uses user_ind_columns.table_name, I
> > like to find total number of rows of all my tables. The output should,
> > for example, be in this format:
> >
> > user-tables total_no_of_rows
> > ----------- -----------------
> > table1 99999
> > table2 99999
> > ....
> > ....
> >
> > Appreciate any of your advise. Thank you for your valuable time.

Hi
You surely can use a PL/SQL procedure or the spool method. An other way is to make a view that counts the tablerows.

If your number of tables is rather constant this migth be a good ide.

You would like to make an sql that generates the view. You can use the spool method here too.

something like this: (not tested)

spool 'create.view.sql';
select 'create or replace view table_count (table_name , number_of_rows)
as select to_char(null),to_number(null)' from dual;

select 'union select '''||table_name ||''', count(1) from ' || table_name
from user_tables;
/

In this way you can easly check the rowcounts. Also you can use ordinary select statments (eg sum , where...) Received on Fri Oct 04 2002 - 11:08:21 CDT