How to Get Counts of all Tables in a Schema in PL/SQL?

Q: How to Get Counts of all Tables in a Schema in PL/SQL?

In database management obtaining insights into the structure and the content of the database tables is crucial for various tasks like performance optimization, resource management, and data analysis. One of the fundamental aspects of this process is determining the number of rows within each table of the schema.

Using PL/SQL Cursors

In database management obtaining insights into the structure and the content of the database tables is crucial for various tasks like performance optimization, resource management, and data analysis. One of the fundamental aspects of this process is determining the number of rows within each table of the schema.

Syntax to get counts of all tables in a schema in PL/SQL:

DECLARE
    v_table_name VARCHAR2(100);
    v_count      NUMBER;
BEGIN
    -- Cursor loop to iterate through tables in the schema
    FOR tables IN (SELECT table_name FROM user_tables) LOOP
        -- Get the table name
        v_table_name := tables.table_name;
        
        -- Construct and execute dynamic SQL to count rows in the table
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_table_name INTO v_count;
        
        -- Output the table name and row count
        DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' has ' || v_count || ' rows.');
    END LOOP;
END;
/

Explanation:

DECLARE keyword is begins the declaration section of PL/SQL block where the variables and cursors are to be declared.
v_table_name is used to hold the names of the each table which can be iterate them.
v_count is variable which is used to store the row count for each table.
BEGIN keyword is used to mark the beginning of the executable section of PL/SQL block.
FOR tables IN (….) LOOP is the cursor loop which is used to iterate the result set of the table names obtained from the querying user_tables.
EXECUTE IMMEDIATE is the statement which is used to execute the dynamic SQL. It will be take a string contains the SQL statements as input and it will execute it.
DBMS_OUTPUT . PUT_LINE procedure is used to output text to the console or output buffer.
END LOOP is used to end the loop.
END; is used to end the block of the PL/SQL.1.