Counting number of records for all the tables – Oracle

In some of our previous posts, we had given some SQL scripts on how to get the counts of the records in all the tables in a database schema in SQL Server. You can access those posts here and here. In this post, we will give a script that can be used in Oracle to do the same thing.  Connect to SQL*Plus with proper credentials and run following PL/SQL block to see the tables and corresponding record count.

/*Script to count the number of rows in tables*/

set serveroutput on

declare
numrows integer;
cursor c1 is select table_name from user_tables order by table_name;
function rowcount(tablename in user_tables.table_name%type)
return integer is
cursornum    integer;
numrows        integer;
ignore        integer;
begin
cursornum := dbms_sql.open_cursor;
dbms_sql.parse(cursornum,
‘select count(*) from ‘ || tablename,
dbms_sql.v7);
dbms_sql.define_column(cursornum, 1, numrows);
ignore := dbms_sql.execute(cursornum);
ignore := dbms_sql.fetch_rows(cursornum);
dbms_sql.column_value(cursornum, 1, numrows);
dbms_sql.close_cursor(cursornum);
return numrows;
end;
begin
dbms_output.enable(100000);
dbms_output.put_line(‘Table                           Rows      ‘);
dbms_output.put_line(‘——————————  ———-‘);
for c1rec in c1 loop
numrows := rowcount(c1rec.table_name);
dbms_output.put_line(rpad(c1rec.table_name, 32) || numrows);
end loop;
end;
/
Here is the output from our schema.

Table                           Rows

------------------------------  ----------

BONUS                           0

DEPT                            4

EMP                             14

EXTERNAL_CITY                   4

INVOICE                         4

SALGRADE                        5

TEMP_INV                        0

TEST                            0

TEST_TABLE                      4

PL/SQL procedure successfully completed.

There are other ways of counting number of records in the table as well. Like getting the NUM_ROWS column value from USER_TABLES view or getting NUM_ROWS value from USER_INDEXES view for a primary key index. But for that number to be accurate, we have to make sure that statistics are always up to date otherwise that number can be wrong. This is the same as getting the number from the sysindexes table in SQL Server.