Andrew Andrew - 11 months ago 53
SQL Question

How to check if a database object in Oracle is a table or view

I have list of object names from which i have to find out whether the object is a table or view. For this reason i have to query and check in all_tables and all_views and confirm whether the object is table or view. I am using below queries and its working. But as i have huge list of object names I want to perform this in single query and check whether the object is table or view and also the owner of the object.

select * from ALL_views where view_name like '%INSTANCE%'

select * from all_tables where table_name like '%INSTANCE%'

Answer Source
select *
  from all_objects 
 where object_name like '%INSTANCE%'

There is an OBJECT_TYPE column in there.