Rows count for every table

Script to show the row count for every table



Run with "SET SERVEROUTPUT ON"

DECLARE
li_count INTEGER;
lv_table VARCHAR2 (61); --30+1+30 : owner||'.'||table_name
BEGIN
FOR c_table IN (SELECT owner, table_name FROM all_tables order by owner, table_name)
LOOP
lv_table := c_table.owner || '.' || c_table.table_name;

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || lv_table INTO li_count;

DBMS_OUTPUT.put_line (rpad(lv_table,62,'.') || li_count);
END LOOP;
END;


--sample of output

MDSYS.OGIS_GEOMETRY_COLUMNS...................................0
MDSYS.OGIS_SPATIAL_REFERENCE_SYSTEMS..........................0
MDSYS.SDO_COORD_AXES..........................................139
MDSYS.SDO_COORD_AXIS_NAMES....................................28
MDSYS.SDO_COORD_OP_METHODS....................................82
MDSYS.SDO_COORD_OP_PARAMS.....................................153
MDSYS.SDO_COORD_OP_PARAM_USE..................................680
MDSYS.SDO_COORD_OP_PARAM_VALS.................................9534

If you want you can in the select put a criterion of some schemes and you could also change the code to store the result (and not showing it in the output) in a table and then you be able to sort by number of rows.