CODEWITHSUNDEEP
sqloracle-databasesubquery
user9715288
user9715288

Reputation:

Oracle Column table Count - Sub-Selection

I really only want the number of columns in the tables in the SQL query, but somehow i can't get the right result. Does anybody know what I'm doing wrong?

select count(*) from user_tab_columns where table_name='tablename' //works and gives me the number of columns


select 
    TABLE_NAME, 
    NUM_ROWS,
    (select count(*) from user_tab_columns where table_name=TABLE_NAME) as Test
from user_tables

Upvotes: 0

Views: 76

Answers (2)

William Robertson
William Robertson

Reputation: 15991

As an alternative, you could aggregate the whole query:

select t.table_name
     , num_rows
     , count(*) as num_columns
from   user_tables t
       join user_tab_columns c on c.table_name = t.table_name
group by t.table_name, t.num_rows
order by 1;

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Haha, look at this:

where table_name=TABLE_NAME

This will always be true, because the table name is the table name.

Here is the query with qualified column names:

select 
  table_name, 
  num_rows,
  (select count(*) from user_tab_columns tc where tc.table_name = t.table_name) as test
from user_tables t;

Upvotes: 1

Related Questions