DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Avg Column Size in a table

  1. #1
    Join Date
    Mar 2001
    Posts
    82
    Hi all,

    Does anyone have a script to give the average column size of each column in a table. I am trying to write one but I am not successful. It gives me the average length of the column name, not the average size of the values.

    Thanks.

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well, it's quite easy, just look for data_type and data_length in dba_tab_columns ...

  3. #3
    Join Date
    Mar 2001
    Posts
    82
    Yeah,
    But what i'm trying to do is a script where I just plug in the name of the table and the script will get all the columns for that table. Then it will get the average length of all rows for each column.

    Ex: ID Name Address
    1 Jim 123 Gordon
    2 Alan 25 Jones
    3 Jane 4 Golden St.

    It should give me the following result:

    ID 1
    Name 3.667
    Address 10

    I can send you a copy of what I have now if you want to play with it.

  4. #4
    Join Date
    Nov 2000
    Posts
    344
    use avg(vsize(column name))

  5. #5
    Join Date
    Mar 2001
    Posts
    82
    That is what i'm (was) using.
    It works fine on its own, but in my procedure,
    the column name comes from a cursor and instead of giving me the avg of the data in the column, it gives the avg of the column name size.

    Anyhow, I found an easier way.

    I didn't notice that dba_tab_columns has an avg_col_length
    column. I just have to get the column name, and the avr_col_length for the table name I give to my procedure.

    Works fine now.

    Denis

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