Sunday, January 17, 2016

Count rows in all tables in a schema

Below PL/SQL block will allow you to count rows in all tables in a given schema: 


Plsql :

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables
              where owner in ('&owner'))
    loop
        execute immediate 'select count(*) from ' ||r.owner||'.'|| r.table_name
            into v_count;
        --INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED) VALUES (r.table_name,r.owner,v_count,SYSDATE);
        DBMS_OUTPUT.PUT_LINE('TABLE OWNER : '||lpad(to_char(r.owner),10)||', Table_name : '||lpad(to_char(r.table_name),10)||
        ', No or rows: '||rpad(to_char(v_count),6));
    end loop;

end;

 /


 Sample output:

TABLE OWNER :      ATEST, Table_name :  TEST_TAB1, No or rows: 11   
TABLE OWNER :      ATEST, Table_name :     LOOKUP, No or rows: 3    
TABLE OWNER :      ATEST, Table_name : BIG_TABLE2, No or rows: 100000
TABLE OWNER :      ATEST, Table_name :  BIG_TABLE, No or rows: 100000


Note :
when run this will prompt you for the schema you want to count the the table rows.You can alter this block to count for all tables in multiple schemas by changing below part to list of schemas like below.

or r in (select table_name, owner from all_tables
              where owner in ('&owner')


You can change the &owner  to 'SCHEMA1','SCHEMA2','SCHEMA3'...

No comments:

Post a Comment