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

Vetry messy but you could extend the following if it REALLY must be one SQL statement. Still not dynamic and probably mor works but you could do thie following...

SELECT SUM(a), SUM(b), SUM(c)
FROM ( SELECT 1 a, 0 b, 0 c from Table1)

     UNION ALL
            SELECT 0, 1, 0 from table2
    UNION ALL
           SELECT 0,0,1 from table3
             )

The result should be a single record, each value would be the number of records in a single table.

Maybe you could generate the above in a single query and then only have one SQL to run in your third party tool.

I agree with theothers though. You're much better off using SQL*Plus for this one.

"shinwar" <jshinwar_at_yahoo.com> wrote in message news:77e5857.0210040506.245b4a90_at_posting.google.com...
> Thanks, Jim
>
> Let me tell you why I could not use those SQL-created-SQL and PL/SQL.
> I need to fire the query I am looking for from a thir-party tool. This
> is why, it is the best and suitable solution if I could have a single
> query for my job. Please advise if there is a way for it. Thanks.
>
>
> "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.
Received on Fri Oct 04 2002 - 08:24:22 CDT