CODEWITHSUNDEEP
sqloracle-databasesql-view
London
London

Reputation: 41

How to count number of columns in a View? - Oracle SQL

I'm trying to figure out a way to count the number of columns in a view in Oracle. There's a lot of answers around on how to count the number of columns in a table, but none for views.

The database has many, many views, and I have to count for each one. It would be most efficient to find a solution in the form of a query I can repeatedly execute - hand counting isn't going to work here.

Thanks for any help you can provide!

Upvotes: 4

Views: 5849

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

The views are in dba_views for all views in the database or in all_views for all views the current user can access. The columns are in dba_tab_cols or all_tab_cols. (There is also user_views and user_tab_cols for the objects owned by the current user.)

You need a join or an IN or EXISTS clause, because the xxx_tab_cols views don't only contain view columns, but also table columns.

select owner, table_name as view_name, count(*)
from dba_tab_cols
where (owner, table_name) in (select owner, view_name from dba_views)
group by owner, table_name
order by owner, view_name;

Upvotes: 2

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

SELECT
      table_name,
      column_name,
      data_type
 FROM all_tab_columns
WHERE table_name = 'VIEWNAME'
  AND owner      = 'OWNER'
ORDER BY column_id;

for counting

SELECT
  count(*) columnCount
FROM all_tab_columns
WHERE table_name = 'VIEWNAME'
  AND owner      = 'OWNER'

Upvotes: 5

Related Questions