Number of columns

Question

how do you count the number of columns in a table in oracle?

Answer 1

SELECT count(*) FROM user_tab_columns WHERE table_name = 'FOO'

should give you the number of columns in foo. You can obtain quite a bit of information from USER_TAB_COLUMNS and USER_TABLES. (There are also ALL and DBA variants)

Answer 2

If Oracle supported INFORMATION_SCHEMA.COLUMNS, I'd say use that. But as others have said, use the USER_% views.

For completeness, the following link describes what systems support the SQL-92 Standard. Systems that support INFORMATION_SCHEMA

Answer 3

@derobert has a good answer, as long as you are trying to count the columns in a table you own. If you need to count columns in another schema's tables, you'll need to use the all_tab_columns view. One of the additional columns in this view is the table owner. This is also useful when the same tablename exists in multiple schemas. Note that you must have privileges on the tables in order to see them in the all_tab_columns view. The query becomes:

select count(*) from all_tab_columns where owner='BAR' and table_name='FOO';

Note the owner and tablename columns are typically upper case.