CODEWITHSUNDEEP
sqldatabaseoracle-database
saroj
saroj

Reputation: 653

sql query to find absent tables from database

I am using ORACLE database. EDIT:

I want only tables not present in a list, not all those tables in database.

Ex: In my database following tables are present:

a
b
c
d
e
r
s

I am being given a list of tables:

a
b
c
v
h

out of which i want to find out which tables are absent(not present) in my database.

so: a, b, c are those tables which are present in my database. So excluding these tables my answer will be:

v and h

My answer is not d,e,r,s

How can i write a query for this to find out?

Upvotes: 0

Views: 659

Answers (5)

xxfelixxx
xxfelixxx

Reputation: 6602

You can use the table and sys.dbms_debug_vc2coll to create a virtual column from your list.

The following should do the trick:

SELECT
    column_value AS missing_table_names
FROM
    TABLE(SYS.DBMS_DEBUG_VC2COLL('a','b','c','v','h'))
WHERE
    column_value NOT IN (
        SELECT
            table_name
        FROM
            ALL_TABLES
    );

Upvotes: 0

Roger Cornejo
Roger Cornejo

Reputation: 1547

I'd do it with the MINUS operator (summary below):

select table_name from table_list
minus
select table_name from db_tables
;

More fully:

with table_list as 
(     select 'A' table_name from dual    
union select 'B' table_name from dual
union select 'C' table_name from dual
union select 'V' table_name from dual
union select 'H' table_name from dual
) 
, db_tables as
(     select 'A' table_name from dual
union select 'B' table_name from dual
union select 'C' table_name from dual
union select 'D' table_name from dual
union select 'E' table_name from dual
union select 'R' table_name from dual
union select 'S' table_name from dual
)
select table_name from table_list
minus
select table_name from db_tables
;

Upvotes: 1

user330315
user330315

Reputation: 

WITH table_names AS ( 
   SELECT 'A' AS tname FROM DUAL
   UNION ALL
   SELECT 'B' FROM DUAL
   UNION ALL
   SELECT 'C' FROM DUAL
   UNION ALL
   SELECT 'D' FROM DUAL
   UNION ALL
   SELECT 'E' FROM DUAL
)
SELECT tn.tname
FROM table_name tn
WHERE NOT EXISTS (SELECT 1  
                  FROM all_tables at
                  WHERE tn.tname = at.table_name);

The WITH part is just a different (re-usable) way of writing a derived table ("inline view").

Upvotes: 1

psaraj12
psaraj12

Reputation: 5072

You can use

SELECT a.table_name FROM (SELECT 'A' as table_name 
               FROM DUAL UNION
               SELECT 'B' as table_name 
               FROM DUAL UNION
               SELECT 'C' as table_name 
               FROM DUAL UNION
               SELECT 'V' as table_name 
               FROM DUAL UNION
               SELECT 'H' as table_name 
               FROM DUAL) a WHERE NOT EXISTS (
SELECT 1 FROM DBA_TABLES b where b.table_name=a.table_name)

Upvotes: 2

Maddy
Maddy

Reputation: 3816

SELECT table_name FROM DBA_tables WHERE table_name IN (Yourlist comes here)

for example

SELECT table_name FROM DBA_tables WHERE table_name NOT IN ('a','b','c');

Upvotes: 0

Related Questions