How to Check If a Table Exist in PL/SQL?

In PL/SQL (Procedural Language/Structured Query Language), it's often necessary to determine whether a particular table exists in the database schema before attempting any operations on it. These views offer detailed metadata about database objects.

This article explores methods and techniques to check for the existence of a table in PL/SQL, providing examples and explanations for each approach.

Checking Table Existence in PL/SQL

The main concept involves querying the data dictionary views provided by the database management system to ascertain the existence of a table. PL/SQL offers various data dictionary views that contain metadata information about database objects, including tables. The syntax typically involves querying these views and checking if the desired table exists based on certain criteria.

Examples of Checking Table Existence in PL/SQL

Example 1: Using USER_TABLES Data Dictionary View

USER_TABLES contains metadata for all user-owned tables. Use this approach to determine if a table exists within the current schema.

DECLARE
  v_table_name VARCHAR2(50) := 'employees';
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM USER_TABLES
  WHERE TABLE_NAME = v_table_name;

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' exists in the current schema.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' does not exist in the current schema.');
  END IF;
END;

• v_table_name: This variable holds the name of the table we want to check for existence.
• v_count: This variable stores the count of tables with the specified name in the current schema.
• SELECT COUNT(*) INTO v_count: This SQL statement selects the count of tables with the specified name from the USER_TABLES data dictionary view.
• IF v_count > 0 THEN: This conditional statement checks if the count is greater than 0, indicating that the table exists.
• DBMS_OUTPUT.PUT_LINE: This procedure outputs a message indicating whether the table exists or not in the current schema.

Explanation:

This PL/SQL block declares a variable v_table_name to hold the name of the table to be checked. It queries the USER_TABLES data dictionary view to count the number of tables with the specified name in the current schema. Based on the count, it prints a message indicating whether the table exists or not.

Example 2: Using DBA_TABLES Data Dictionary View (for privileged users)

DBA_TABLES includes metadata for all database tables but requires DBA privileges. Ideal for checking tables across schemas

DECLARE
  v_table_name VARCHAR2(50) := 'employees';
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM DBA_TABLES
  WHERE OWNER = USER AND TABLE_NAME = v_table_name;

  IF v_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' exists in the database.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Table ' || v_table_name || ' does not exist in the database.');
  END IF;
END;

• v_table_name: Same as in the first approach, this variable holds the name of the table we want to check for existence.
• v_count: Same as in the first approach, this variable stores the count of tables with the specified name.
• SELECT COUNT(*) INTO v_count: Similar to the first approach, this SQL statement selects the count of tables with the specified name, but from the DBA_TABLES data dictionary view.
• WHERE OWNER = USER AND TABLE_NAME = v_table_name: This condition ensures that the table belongs to the current user/schema.
• IF v_count > 0 THEN: Same as in the first approach, this conditional statement checks if the count is greater than 0, indicating that the table exists.
• DBMS_OUTPUT.PUT_LINE: Similar to the first approach, this procedure outputs a message indicating whether the table exists or not in the entire database.

Explanation:

This PL/SQL block is similar to Example 1 but queries the DBA_TABLES data dictionary view (which requires privileged access) to count the number of tables with the specified name in the entire database. It prints a message indicating whether the table exists or not based on the count.

Conclusion

Both approaches utilize data dictionary views to query metadata information about tables. The first approach checks within the current schema, while the second approach checks within the entire database. The choice between these approaches depends on the level of privilege and the scope of the search required.