How to use the USER_TABLES View In SQL
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 of the Code:
- 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.
- The above approach is simplify the process by focussing only on the tables owned by current user which is might be the beneficial in the scenarios where we are interested in the managing or analyzing tables in our own schema.
How to Display all Tables in PL/SQL?
In 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.