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!
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'
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;
