oracle-databaseplsql

Get counts of all tables in a schema


I am trying to get the record counts of all tables in a schema. I am having trouble writing the PL/SQL. Here is what I have done so far, but I am getting errors. Please suggest any changes:

DECLARE
v_owner varchar2(40);
v_table_name varchar2(40);

cursor get_tables is
select distinct table_name,user
from user_tables
where lower(user) = 'SCHEMA_NAME';


begin

open get_tables;
fetch get_tables into v_table_name,v_owner;

    INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
    SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM         v_table_name;

CLOSE get_tables;

END;

Solution

  • This should do it:

    declare
        v_count integer;
    begin
    
        for r in (select table_name, owner from all_tables
                  where owner = 'SCHEMA_NAME') 
        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);
        end loop;
    
    end;
    

    I removed various bugs from your code.

    Note: For the benefit of other readers, Oracle does not provide a table called STATS_TABLE, you would need to create it.