Re: find max length of data in all columns?

susana73_at_hotmail.com (Susan Lam) wrote in message news:<7186ed56.0212102328.77cd2980_at_posting.google.com>...
> I'd like to find the max length of data in all the varchar2 columns of
> the entire db. To do the following query column by column will take a
> long time as the db is huge.
> select max(length(col_name1)) from table_name1;
>
> Is there any system tables that store such an information?
>
> Thanks,
> Susan

Susan, please do not cross-post on the Oracle boards.

If you only need to know the maximum possible length then you can look at dba_tab_columns, but if you need to know the maximum length for actual data stored then you will have to query each and every table that has a varchar2 column defined over some threshold. That is if column c6 of table x has a lenth of 2200 then you know you do not need to check any column that isn't defined as >= 2201.

Long and LOB columns can also be used to store character data.

HTH -- Mark D Powell -- Received on Wed Dec 11 2002 - 12:49:59 CST