3

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.

1
  • You either query a database catalog view or access the DatabaseMetaData object.
    – mustaccio
    Commented Mar 5, 2016 at 15:07

2 Answers 2

8

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:

3

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.