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

In Database Management System, it is essential to retrieve the statistical information about tables with the schema. Whether it is for monitoring the database health, optimizing the performance, or simply understanding the data structures having access to row counts of the tables can be more valuable.

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.

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.

Using PL/SQL Cursors

Using PL/SQL cursors are an effective method for the retrieving the data from database and it process it row by row. Cursors are allowed to the iterate through the result set returned by the SQL query and enabling to the perform operations on each row individually.

Step 1: Create a table and name it as employee.

Here is the employee table.

Employee table

Step 2: Implement the Code

DECLARE
    v_total_salary NUMBER := 0;
BEGIN
    FOR emp_record IN (SELECT * FROM employee) LOOP
        v_total_salary := v_total_salary + emp_record.salary;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Total Salary: ' || v_total_salary);
END;
/

Output:

Output

Explanation:

• We declare the variable v_total_salary to store the total salary is initialized to 0.
• We can use cursor loop to the iterate the each row of the employee table. The cursor emp_record holds the current row data.
• In the loop, we increment the v_total_salary by adding the salary of the employee.
• Once the loop is finished, we print the total salary with the help of DBMS_OUTPUT.PUT_LINE.

Using PL/SQL Procedures

Using the PL/SQL procedure is the structured way to the encapsulate the series of SQL and PL/SQL statements into reusable units of the code. Procedures can be accept the input parameters, perform the operations and it returns output values. These are mainly useful for the modularizing the code, improving the maintainability and promoting the code reuse.

Let us take same employee table to retrieve the average salary for the specific department.

Step 1: Create or Replace the procedure

CREATE OR REPLACE PROCEDURE CalculateAvgSalary(
    department_id IN NUMBER,
    avg_salary OUT NUMBER
)
AS
BEGIN
    SELECT AVG(salary)
    INTO avg_salary
    FROM employee
    WHERE DepartmentID = department_id;
END;
/

Explanation:

• We can create the PL/SQL procedure and named as CalculateAvgSalary with the two parameters such as department_id and avg_salary.
• In the Procedure, we use the SQL query to calculate the average salary of the employee which is related to the specific department.
• The query result is stores in the avg_salary output parameter with the help of INTO clause.

Step 2: Calling the Procedure

DECLARE
    v_avg_salary NUMBER;
BEGIN
    CalculateAvgSalary(1, v_avg_salary);
    DBMS_OUTPUT.PUT_LINE('Average Salary for Department 1: ' || v_avg_salary);
END;
/

For call the procedure and retrieve the average salary for the specific department, you can execute the above PL/SQL block.

Output:

Output

In the above output, average salary for Department 1 is 5000.

Conclusion

Overall, using PL/SQL for retrieve the row counts for all the tables in a schema provided the powerful and efficient way to the gather statistical information about the database tables. By dynamically querying system tables and execute the dynamic SQL statements, we will automate the process of the counting rows in the each table. This approach is particularly used for the tasks like monitoring database health and identifying the performance bottlenecks and understanding the data distribution in the database schema. By incorporating the PL/SQL-based solutions into the database management workflows and organisations can be streamline monitoring, optimizing and decision making process. To retrieve the counts of all the tables in a schema enhances effectiveness and efficiency of the database administration and tasks of the development and it contributing the overall success of database-driven applications and systems.