How to Display all Tables in PL/SQL?

In Oracle PL/SQL, we need to work with database objects like tables. It provides various approaches to display all the tables. Such as using the USER_TABLES, ALL_TABLES, and DBA_TABLES views. Each approach is explained with syntax and examples. In this article, We will learn about How to Display all Tables in PL/SQL by understanding various methods and so on in detail.

Different Approaches to Display Tables in PL/SQL

When working with PL/SQL, there are several methods to display all tables in a database schema. Each method has its advantages depending on specific requirements. Below is the method that helps us to display all tables in PL/SQL are as follows:

Syntax

DECLARE
    v_table_name all_tables.table_name%TYPE;
BEGIN
    FOR table_rec IN (SELECT table_name FROM all_tables) LOOP
        v_table_name := table_rec.table_name;
        DBMS_OUTPUT.PUT_LINE(v_table_name);
    END LOOP;
END;
/

Explanation:

• DECLARE is the beginning of the PL/SQL block. This block is used to declare the variables.
• v_table_name is declare the variable v_table_name of type all_tables . table_name%TYPE which is stores the table names retrieved from query.
• BEGIN is the mark for the beginning of the execution section of PL/SQL.
• FOR Loop is initiates the loop iterates through each row returned by SQL query.
• v_table_name is used to assigns the current table name from loop iteration to the v_table_name variable.
• DBMS_OUTPUT.PUT_LINE is used to print the statements in the output console.
• END LOOP is mark the loop end.
• END is mark the end of the executable section of PL/SQL block.

Setting Up the Environment

To understand How to display all tables in PL/SQL we need a some table on which we will perform various operations and queries. Here we will consider some table are shown below:

Departments Table:

Departments Table:

Orders Table:

Approach 1: Using the USER_TABLES View

The USER_TABLES view displays tables that are specifically owned by the current user. This is useful for users who only need to work within their schema.

Suppose we have to display the names of all tables owned by the current user in a PL/SQL block using a cursor and the DBMS_OUTPUT.PUT_LINE procedure.

DECLARE
    v_table_name user_tables.table_name%TYPE;
BEGIN
    FOR table_rec IN (SELECT table_name FROM user_tables) LOOP
        v_table_name := table_rec.table_name;
        DBMS_OUTPUT.PUT_LINE('Table Name: ' || v_table_name);
    END LOOP;
END;
/

Output:

Table Name: Employees
Table Name: Departments
Table Name: Orders

PL/SQL procedure successfully completed.

Explanation:

• The USER_TABLES view contains the information about the tables owned by the current user.
• The PL/SQL block is iterate through the each table name retrieved from USER_TABLES view using the cursor.
• For each table name is fetched, it is print table name with the help of DBMS_OUTPUT.PUT_LINE.

Approach 2: Using the ALL_TABLES View

The ALL_TABLES view allows users to see all tables accessible to them, including tables owned by other users but accessible through permissions. This method is beneficial for users who need a broader view of the database schema, especially in collaborative environments.

Suppose we have to display the names of all tables accessible to the current user in a PL/SQL block using a cursor and the DBMS_OUTPUT.PUT_LINE procedure.

DECLARE
    v_table_name all_tables.table_name%TYPE;
BEGIN
    FOR table_rec IN (SELECT table_name FROM all_tables) LOOP
        v_table_name := table_rec.table_name;
        DBMS_OUTPUT.PUT_LINE('Table Name: ' || v_table_name);
    END LOOP;
END;
/

Output:

Table Name: Employees
Table Name: Departments
Table Name: Orders

PL/SQL procedure successfully completed.

Explanation:

• The ALL_TABLES view contains the information about the tables access to the current user.
• The PL/SQL block is iterate through the each table name retrieved from ALL_TABLES view using the cursor.
• For each table name is fetched, it is print table name with the help of DBMS_OUTPUT.PUT_LINE.

Approach 3: Using the DBA_TABLES View

The DBA_TABLES view lists every table within the Oracle database, but it is only accessible to users with DBA privileges. This view is ideal for database administrators who need to perform tasks such as database-wide audits, schema analyses, or table management across the entire database.

Suppose have to display the names of all tables in the database accessible to the user with DBA privileges in a PL/SQL block using a cursor and the DBMS_OUTPUT.PUT_LINE procedure.

DECLARE
    v_table_name dba_tables.table_name%TYPE;
BEGIN
    FOR table_rec IN (SELECT table_name FROM dba_tables) LOOP
        v_table_name := table_rec.table_name;
        DBMS_OUTPUT.PUT_LINE('Table Name: ' || v_table_name);
    END LOOP;
END;
/

Output:

Table Name: Employees
Table Name: Departments
Table Name: Orders

PL/SQL procedure successfully completed.

Explanation:

• The DBA_TABLES view is contain the information about the all tables database.
• The PL/SQL block is iterate through the each table name retrieved from DBA_TABLES view using the cursor.
• For each table name is fetched, it is print table name with the help of DBMS_OUTPUT.PUT_LINE.

Conclusion

Overall, displaying all tables in a PL/SQL environment is a task with multiple approaches. If using the USER_TABLES, ALL_TABLES, or DBA_TABLES view, developers and database administrators can easily retrieve and display table names based on their specific requirements. Understanding these methods enhances the ability to manage and analyze database schemas effectively in a PL/SQL environment.