COUNT ROWS FROM ALL TABLES AND PRINT THEM - plsql - oracle

Count rows in all tables or similar tables in database:

I have come across many situations as a DBA when I have to count the total number of rows in a tables in a table across the database. I have written this simple code to help us with that.

If you are a DBA you might also say why not just query the num_rows from all_tables or user_tables. But that is not possible as it might not give you the accurate count, as we all know that you cant get the right numbers with all_tables column, at least not until you have gather the stats for all these tables before you run the query.




-----   COUNT ROWS FROM ALL TABLES AND PRINT THEM :


DECLARE
  result sys_refcursor;
  strTableOwner VARCHAR2(100);
  strTableName1 VARCHAR2(100);
  strQuery      VARCHAR2(4000);
  rec           NUMBER;
BEGIN
  OPEN result FOR SELECT owner,table_name FROM all_tables WHERE table_name IN (
  'ABC') order by owner;    -->> you can alter this query as per your requirement
  LOOP
    FETCH
      result
    INTO
      strTableOwner,
      strTableName1;
    EXIT
  WHEN result%notfound;
    --- List schema and table being counted
    DBMS_OUTPUT.PUT_LINE('count for '||strTableOwner||'.'||strTableName1||' ');
    strQuery := 'select count(*) from '||strTableOwner||'.'||strTableName1||' ';
    EXECUTE immediate strQuery INTO rec;
    DBMS_OUTPUT.PUT_LINE(' >> '||rec||'');
  END LOOP;
  CLOSE result;
END;




Sample Output :

count for ATOORPU.ABC IS
 >> 5
count for SCOTT.ABC IS
 >> 1
count for SYS.ABC IS
 >> 8


Note : 

1) You can edit this plsql as per your requirement, it can count all tables in a schema if change all_tables to user_tables.

2) You can also get the count of different tables by changing the In clause in the cursor.