get table record count in oracle

want to count records for all the tables in a schema. For example,
SQL> select table_name from user_tables order by table_name;
T1
T2
T3

3 rows selected.

SQL> select count(1) from t1;
         2
Can one complex query be written to get the records per table? The result is
table_name   count_records
T1                 2
T2                 5
T3                 4
...
jl66Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sdstuberConnect With a Mentor Commented:
if your statistics are up to date you can get close approximations by selecting "num_rows" from user_tables

select table_name,num_rows from user_tables;

if you really want to count all of the rows...


select table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
          ,'/ROWSET/ROW/X')) count
from user_tables

note, if you have many large tables this could take a long time.
0
 
jl66Author Commented:
Excellent!
Is it possible to loop all tables in one schema and get certain columns out in each table?
0
 
sdstuberCommented:
I'm not sure what you're asking.

If I've answered your original question please close this question.

When you open a new question, please include sample input and expected output to illustrate what you're looking for.
When you close this question there will be an option to ask a related question if you want to tie these two together
0
 
jl66Author Commented:
Excellent.
0
 
sdstuberCommented:
glad I could help
0
All Courses

From novice to tech pro — start learning today.