CODEWITHSUNDEEP
sqldatabaseoracle-database
user2692751
user2692751

Reputation: 101

Query to display all tables and views

I've come up with a query that displays all tables and views of a specific owner. What I would like to do now, but am having an issue with, is that I would like to have a second column which line by line will indicate whether the field is a "table" or "view". Is this possible? if so, how might I go about this?

select table_name
 from all_tables
 where owner = '<owner>'
 UNION
 select view_name
 from all_views
 where owner = '<owner>'
 order by table_name;

Upvotes: 1

Views: 3814

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

I'd prefer the xxx_objects views myself for this purpose (as Justin says), but if you specifically need other data from the table and view views, you can add extra info thus:

select 'Table' AS object_type, table_name
from all_tables
where owner = '<owner>'
UNION ALL
select 'View' AS object_type, view_name
from all_views
where owner = '<owner>'
order by table_name;

Note I've changed it to use UNION ALL because there will be no collisions between the two result sets.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231811

I'd use all_objects instead

select object_name, object_type
  from all_objects
 where object_type in ('TABLE', 'VIEW')
   and owner = <<schema name>>
 order by object_name

Upvotes: 1

Related Questions