how to join
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: how to join

  1. #1
    Join Date
    Feb 2001
    Posts
    44
    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....

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by svk
    Remember, DBA_TAB_COLUMNS will have entries for views as well.
    Ah, I stand corrected!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width