How to count the number of columns in a table using SQL?
I am using Oracle 11g
Please help. t.
1
-
2Have you tried this? stackoverflow.com/questions/506176/number-of-columnsCatalin– Catalin2012-04-10 07:01:17 +00:00Commented Apr 10, 2012 at 7:01
Add a comment
|
3 Answers
select count(*)
from user_tab_columns
where table_name='MYTABLE' --use upper case
Instead of uppercase you can use lower function. Ex: select count(*) from user_tab_columns where lower(table_name)='table_name';
5 Comments
Indrajeet Gour
nothing has there in table user_tab_columns
BekaBot
did not work for me
Mike Argyriou
It worked but the table name is case sensitive.
Exodus
Is there a column that identifies the schema? What if there are two tables with the same name in different schema -- which one would 'user_table_columns' show?
Exodus
Nvm -- just realize that should use 'all_tab_columns' in that case
Maybe something like this:
SELECT count(*) FROM user_tab_columns WHERE table_name = 'FOO'
this will count number of columns in a the table FOO
You can also just
select count(*) from all_tab_columns where owner='BAR' and table_name='FOO';
where the owner is schema and note that Table Names are upper case
1 Comment
zygimantus
If i need not only count value but also particular column value, is it possible to append it easily?
Old question - but I recently needed this along with the row count... here is a query for both - sorted by row count desc:
SELECT t.owner,
t.table_name,
t.num_rows,
Count(*)
FROM all_tables t
LEFT JOIN all_tab_columns c
ON t.table_name = c.table_name
WHERE num_rows IS NOT NULL
GROUP BY t.owner,
t.table_name,
t.num_rows
ORDER BY t.num_rows DESC;
