Oracle – Table Row count for all tables

Here is a simple SQL script with the help we can find the Table Row count for all tables.

Yes we can find the row count using select count(*) for each table. This will be useful if you large number of tables are there in your database.

Example Script:

-- Create a table 
CREATE TABLE ROW_COUNT_ALL_TABLE
(
TABLE_NAME VARCHAR2(100),
ROW_COUNT  VARCHAR2(100)
)

-- PL-SQL Block to count Row Count for all tables as you specify

DECLARE 
V_ROW_COUNT      VARCHAR2(100);
V_SQL_STRING     VARCHAR2(1000);
BEGIN
FOR X IN ( SELECT OBJECT_NAME FROM ALL_OBJECTS  
WHERE OBJECT_TYPE = 'TABLE' AND  OWNER = 'SCOTT' 
AND OBJECT_NAME != 'ROW_COUNT_ALL_TABLE' )
LOOP 
    V_SQL_STRING := 'SELECT COUNT(*) FROM  ' ||X.OBJECT_NAME;
    EXECUTE IMMEDIATE V_SQL_STRING INTO V_ROW_COUNT;     
    INSERT INTO ROW_COUNT_ALL_TABLE VALUES (X.OBJECT_NAME, V_ROW_COUNT);
    COMMIT;
END LOOP;
END;

Required Result as