Counting number of records for all the tables – Oracle
Posted by decipherinfosys on September 21, 2007
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
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 := dbms_sql.open_cursor;
‘select count(*) from ‘ || tablename,
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_output.put_line(‘Table Rows ‘);
for c1rec in c1 loop
numrows := rowcount(c1rec.table_name);
dbms_output.put_line(rpad(c1rec.table_name, 32) || numrows);
Here is the output from our schema.
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.
2 Responses to “Counting number of records for all the tables – Oracle”
Sorry, the comment form is closed at this time.