Number of columns Number of columns sql sql

Number of columns

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).

@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.

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