Row Counts

Dear Team,

I need a query showing record counts for all tables in your database.
For e.g.

Suppose there are three tables A,B,C

The query should return

Table Name Record Count
__________________________________
A 1000
B 200
C 1750

Without using script

select table_name, num_rows from user_tables order by table_name;
OR
select table_name, num_rows from ALL_tables
order by table_name;

[QUOTE=susarlasireesha;21756]select table_name, num_rows from user_tables order by table_name;
OR
select table_name, num_rows from ALL_tables
order by table_name;[/QUOTE]


Dear susarlasireesha,

I had already tried the above two queries before. But its not giving the correct result for all tables.

Is there any other option.

Thanks for your kind reply

If you are trying forall the tables in the database ,you may not have required perviliges for the same.

[QUOTE=debasisdas;21776]If you are trying forall the tables in the database ,you may not have required perviliges for the same.[/QUOTE]

Dear debasisdas,

Thank you for your kind reply.

I am trying this only for FINANCE schema for which i have all the privileges.
There are more than 300 tables which fall under my schema.

[QUOTE=sreekumar_nair_it;21763]Dear susarlasireesha,

I had already tried the above two queries before. But its not giving the correct result for all tables.

Is there any other option.

Thanks for your kind reply[/QUOTE]

Try this
Sql > Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';'
from user_tables
order by table_name
Sql > Set heading off
Sql > Spool tablescount
Sql > /
Sql > Spool off
Sql > @ tablescount

[QUOTE=susarlasireesha;21831]Try this
Sql > Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';'
from user_tables
order by table_name
Sql > Set heading off
Sql > Spool tablescount
Sql > /
Sql > Spool off
Sql > @ tablescount[/QUOTE]

Dear susarlasireesha,

Thank you for your kind reply.

But i needed a single query instead of script.

use This but This must be run in sys user other wise
ur user must have sufficient privileges
select
table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from ALL_tables ;