Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to count total number of rows from all user-tables
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.
Received on Thu Oct 03 2002 - 20:27:55 CDT