CODEWITHSUNDEEP
sqloracle-databaseschema
Beth
Beth

Reputation:

Number of columns

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

Upvotes: 11

Views: 16240

Answers (5)

Ray K
Ray K

Reputation: 1490

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;

Upvotes: 0

sanjay Poshiya
sanjay Poshiya

Reputation: 1

Number of column and number of rows in oracle table:

SELECT u.table_name Table_Name, 
       Count(*)     Table_Columns, 
       u.num_rows   Table_Rows 
FROM   user_tab_columns c, 
       user_tables u 
WHERE  u.table_name = c.table_name 
GROUP  BY u.table_name, 
          u.num_rows

Upvotes: 0

derobert
derobert

Reputation: 51157

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

Upvotes: 20

DCookie
DCookie

Reputation: 43533

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

Upvotes: 10

beach
beach

Reputation: 8640

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

Upvotes: 1

Related Questions