-
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
-
Re: Row Counts
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;
-
Re: Row Counts
[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
-
Re: Row Counts
If you are trying forall the tables in the database ,you may not have required perviliges for the same.
-
Re: Row Counts
[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.
-
Re: Row Counts
[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
-
Re: Row Counts
[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.
-
Re: Row Counts
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 ;