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.

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

   row_coun   NUMBER (10);
   FOR cur IN c1
      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);

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


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s