-
I do this.
select count(*) from all_objects
where object_type='TABLE';....this is where I get the total table counts for object_type table.
but I want to get count for total columns on those objects....
how do I do this?
select (column_name) from all_objects, dba_tab_columns
where object_type='TABLE';.....I know this is not right...how do I do this?
Thanks....
-
Something like:
Code:
SQL> l
1 select column_name, count(*)
2 from dba_tab_columns
3 group by column_name
4* order by count(*) desc
SQL> /
COLUMN_NAME COUNT(*)
------------------------------ ----------
OWNER 388
NAME 317
OWNERID 211
INST_ID 187
TYPE 153
TABLE_NAME 152
PROPERTY 109
STATUS 109
OBJID 101
OBJ# 98
COLUMN_NAME 85
COLUMN_NAME COUNT(*)
------------------------------ ----------
TNAME 76
ENABLED 66
BLOCKS 64
TABLESPACE_NAME 63
PCTFREE$ 62
MAXTRANS 61
FLAGS 61
INITRANS 61
MTIME 58
SPARE1 57
SPARE2 55
CACHE 52
24 rows selected.
Jeff Hunter
-
Remember, DBA_TAB_COLUMNS will have entries for views as well. And I guess, your requirement is this :
select a.table_name,count(column_name) total_columns
from dba_tables a,dba_tab_columns b
where a.table_name=b.table_name and a.owner=b.owner
group by a.table_name;
svk
-
Originally posted by svk
Remember, DBA_TAB_COLUMNS will have entries for views as well.
Ah, I stand corrected!
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|