Count(*) of all tables

[Halo]

Does anyone have a script to select table_name and count(*) from all tables?? Thanx.

[rsuri]

come on Halo, I don't expect this from you...can't you use a dynamic sql here :-)

[pando]

cant you just analyze the schema or database and check num_rows from dba_tables?

[Halo]

Tell me about it. I'm brain ded today.

[rsuri]

select 'select ''' || table_name || ''', count(*) from ' || table_name || ';' from user_tables

[Halo]

Well, I haven't been given system privs on this db and so cannot analyze it.
-
RSURI: the script did not work!!

[chrisrlong]

It looks fine to me...?

It should generate a set of statements that can be output to a file and then run.

What is wrong with it?

Did they maybe not give you rights to USER_TABLES?

- Chris

[rsuri]

I think I messed up one quote; Try this --

select 'select ''' || table_name || ''',' || ' count(*) from ' || table_name || ';' from user_tables;

Here is the result --


SQL> select 'select ''' || table_name || ''',' || ' count(*) from ' || table_name || ';'
2 from user_tables;

'SELECT'''||TABLE_NAME||''','||'COUNT(*)FROM'||TABLE_NAME||';'
--------------------------------------------------------------------------------------
select 'A1', count(*) from A1;
select 'COURSES', count(*) from COURSES;
select 'NODES', count(*) from NODES;
select 'PROGRESS', count(*) from PROGRESS;
select 'QUEST_IM_BUFFER_BUSY', count(*) from QUEST_IM_BUFFER_BUSY;
select 'QUEST_IM_EVENT_CATEGORIES', count(*) from QUEST_IM_EVENT_CATEGORIES;
select 'QUEST_IM_LOCK_TREE', count(*) from QUEST_IM_LOCK_TREE;
select 'QUEST_IM_MONITOR_VERSION', count(*) from QUEST_IM_MONITOR_VERSION;
select 'QUEST_IM_PLAN_TABLE', count(*) from QUEST_IM_PLAN_TABLE;
select 'T2', count(*) from T2;
select 'T4', count(*) from T4;
select 'TABLE_SIZES', count(*) from TABLE_SIZES;
select 'TEST', count(*) from TEST;
select 'TEST1', count(*) from TEST1;
select 'TEST2', count(*) from TEST2;
select 'TEST3', count(*) from TEST3;
select 'UPCATS', count(*) from UPCATS;
select 'UPINT', count(*) from UPINT;
select 'UPROFS', count(*) from UPROFS;
select 'USERS', count(*) from USERS;

20 rows selected.

SQL>

[rsuri]

This is for you Chris...

user_tables is view accesible to everyone; So even a user with just "create session" privilege should be able to see what tables he owns (he won't own any tables though if all he has is "create session" privilege :-)

- Rajeev

[Halo]

I made a mistake. Duh!! It works fine. Thanx Rajeev.