How to count the number of columns in a table using SQL? [duplicate]

Question

How to count the number of columns in a table using SQL?

I am using Oracle 11g

Please help. t.

Answer 1

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

Answer 2

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

Answer 3

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;