How To Count Rows Of Each Table Of A Schema

Posted: October 12, 2013 in Database General
Tags: ,

Most of us have encounterd the situation where to count rows of each tables in a particular schema. Straight forward approach is to run a query which will generate a script for this task and then run that script. Below is small block which is much easier to run and can be customized according to requirment.

DECLARE
   CURSOR c1
   IS
      SELECT   owner, table_name
        FROM   dba_tables
       WHERE   owner = 'MYUSER';

   row_coun   NUMBER (10);
BEGIN
   FOR cur IN c1
   LOOP
      EXECUTE IMMEDIATE   'select count(*) from '
                       || cur.owner
                       || '.'
                       || cur.table_name
         INTO   row_coun;

      DBMS_OUTPUT.put_line (RPAD (cur.table_name, 20) || CHR (9) || row_coun);
   END LOOP;
END;

Since, it’s using DBA_TABLES, so above code should be run with user having read access this view.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s