Check if Table, View, Trigger, etc present in Oracle

To check for the existence of various database objects such as tables, views, triggers, and others in an Oracle database, you can query the Oracle data dictionary. Here's how you can check for each:

Table:

Check the ALL_TABLES, USER_TABLES, or DBA_TABLES views:

SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'YOUR_TABLE_NAME';

View:

Check the ALL_VIEWS, USER_VIEWS, or DBA_VIEWS views:

SELECT * FROM ALL_VIEWS WHERE VIEW_NAME = 'YOUR_VIEW_NAME';

Trigger:

Check the ALL_TRIGGERS, USER_TRIGGERS, or DBA_TRIGGERS views:

SELECT * FROM ALL_TRIGGERS WHERE TRIGGER_NAME = 'YOUR_TRIGGER_NAME';

Procedure, Function, Package, etc.:

Check the ALL_OBJECTS, USER_OBJECTS, or DBA_OBJECTS views:

SELECT * 
FROM ALL_OBJECTS 
WHERE OBJECT_NAME = 'YOUR_OBJECT_NAME' 
AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', ...);

In each of the examples above:

ALL_ prefixed views will show objects to which you have access, including those owned by others.
USER_ prefixed views will show only objects that you own.
DBA_ prefixed views will show all objects in the database, but you need appropriate permissions to query these views.

To determine whether a specific object exists, you can adjust the WHERE clause in the query and check for rows in the result. If rows are returned, then the object exists. If no rows are returned, then the object does not exist.

Oracle SQL check if table exists:

SELECT table_name
FROM user_tables
WHERE table_name = 'YOUR_TABLE_NAME';

Query to verify the existence of a view in Oracle:

SELECT view_name
FROM user_views
WHERE view_name = 'YOUR_VIEW_NAME';

Checking if a trigger exists in Oracle database:

SELECT trigger_name
FROM user_triggers
WHERE trigger_name = 'YOUR_TRIGGER_NAME';

How to determine if a table is present in Oracle SQL:

SELECT table_name
FROM user_tables
WHERE table_name = 'YOUR_TABLE_NAME';

Oracle query to check the existence of a specific view:

SELECT view_name
FROM user_views
WHERE view_name = 'YOUR_VIEW_NAME';

Checking for the presence of a trigger in Oracle database:

SELECT trigger_name
FROM user_triggers
WHERE trigger_name = 'YOUR_TRIGGER_NAME';

SQL script to test if a table exists in Oracle:

DECLARE
   table_count NUMBER;
BEGIN
   SELECT COUNT(*)
   INTO table_count
   FROM user_tables
   WHERE table_name = 'YOUR_TABLE_NAME';

   IF table_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('Table exists');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Table does not exist');
   END IF;
END;

Oracle SQL query to check if a procedure exists:

SELECT object_name
FROM user_objects
WHERE object_name = 'YOUR_PROCEDURE_NAME'
AND object_type = 'PROCEDURE';

Determining the existence of a function in Oracle:

SELECT object_name
FROM user_objects
WHERE object_name = 'YOUR_FUNCTION_NAME'
AND object_type = 'FUNCTION';

Checking for the presence of an index in Oracle database:

SELECT index_name
FROM user_indexes
WHERE table_name = 'YOUR_TABLE_NAME'
AND index_name = 'YOUR_INDEX_NAME';