Oracle :: How to query all table columns except views

As per the documentation provided in the following page, once you query ALL_TAB_COLUMNS, database views and view columns are also included. There’s nowhere specified whether the table is actually a table or a view.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

However, with the following SQL it’s easy to exclude views and retrieve table columns only.

select 
  table_name,
  column_name,
  data_type,
  data_length,
  column_id
from
  all_tab_columns
where
  owner = 'SCHEMA_NAME' and
  table_name not in (select view_name from all_views where owner = 'SCHEMA_NAME')
order by 1, 2, 3;

It saved my day! 🙂

Advertisements

3 thoughts on “Oracle :: How to query all table columns except views

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s