Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL row count all tables

895156Oct 19 2011 — edited Oct 19 2011
Hello,

I'm trying to draft up a PL/SQL script in SQL Developer 1.5.1 that does the following:

Once a database is loaded, display each table name within the database and the row count associated with each table.

The script needs to be generic i.e. I should be able to execute it within any database and work as intended.

So far, I've only been able to pull up a list of all database tables using 'user_tables'.

------------------------------------------------------------------------------------
DECLARE
tab_name VARCHAR2(40);
tab_count VARCHAR2(40);
curs_tab user_tables.table_name%TYPE;
CURSOR c1 IS
SELECT table_name
FROM user_tables;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO curs_tab;
EXIT WHEN c1%NOTFOUND;
tab_name := curs_tab;
--SELECT COUNT(*) INTO tab_count FROM c1;
DBMS_OUTPUT.PUT_LINE (curs_tab || ' ' || tab_count);
END LOOP;
CLOSE c1;
END;
------------------------------------------------------------------------------------

My intention is as follows: when each row from 'user_tables' is passed through the cursor, store that name into a variable 'tab_name'. Then use 'tab_name' in a SELECT COUNT (*) statement to get a row count, a la the commented line. I briefly considered creating a list of cursors within each cursor, much like an array, however this seemed too complicated to work...

Does anyone have any ideas on how to achieve this? The boss just wants the script to run with the click of a button, nothing more.

Thanks in advance.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 16 2011
Added on Oct 19 2011
7 comments
10,505 views