Check if multiple tables exist

Question

The idea is to select 2 column response representing the table name and a boolean flag if it exists in the database for predefined list of tables. Is it possible to make such request in a single query?

https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle — juergen d, Sep 06 '19 at 08:42
I do not need list of all tables, the idea is to check if some tables like 'TableA', 'TableB', 'TableC' exist and get response like TableA - true TableB - falseTableC - true — Sql Neofite, Sep 06 '19 at 08:45

score 1 · Accepted Answer · answered Sep 06 '19 at 08:44

You can use USER_TABLES dictionary view. This view contains detail of the all tables that is owned by the current user.

You can achieve the desired result using following query:

WITH PREDEFINED_LIST_TABLES AS
(SELECT 'TABLE1' TABLE_NAME FROM DUAL UNION ALL
....
....)
SELECT P.TABLE_NAME , 
CASE WHEN U.TABLE_NAME IS NOT NULL THEN 1 ELSE 0 END AS TAB_EXISTS
FROM PREDEFINED_LIST_TABLES P 
LEFT JOIN USER_TABLES U
ON (P.TABLE_NAME = U.TABLE_NAME);

Cheers!!

Check if multiple tables exist

1 Answers1