Finding the number of columns a table has

Question

How can I find out the number of columns a table has?

I use oracle 10g and I want to develop a J2EE application to count the number of columns of different tables.

Answer 1

That's what the system catalogs are for:

select count(*)
from all_tab_columns
where owner = 'SOME_USER'
  and table_name = 'SOME_TABLE';

To get it per table, use a `group by

select table_name, count(*)
from all_tab_columns
where owner = 'SOME_USER'
group by table_name
order by table_name;

More details about the system catalogs can be found in the manual:

• ALL_TAB_COLUMNS
• Data Dictionary Views

Answer 2

You can query this system table:

SELECT table_name, count(*)
FROM user_tab_columns 
WHERE table_name = '...';

Add a GROUP BY if you need to query more than one table:

SELECT table_name, count(*)
FROM user_tab_columns 
WHERE table_name IN ('...', '...')
GROUP BY table_name;