RE: Get max length of data for a column

You can, of course, build a script to figure them all out. For example

select 'select '''||table_name||'.'||column_name||''', max(length('||column_name||')) from '||table_name||';'

from user_tab_columns where data_type = 'VARCHAR2';

will build a bunch of statements like

select 'WT_SWIMS_FIELDWORK.FIELD_DESC', max(length(FIELD_DESC)) from WT_SWIMS_FIELDWORK;
;
Run them as a script and you'll get dozens or hundreds of grids like:

WT_SWIMS_FIELDWORK.DYN_FORM_CODE 18

But it'll be a big performance hit. For LOBs, use max(DBMS_LOB.getlength(lob_column_name)).

-- jim

________________________________
From: [email protected] [mailto:[email protected]] On Behalf Of Simoneau, Roger
Sent: Tuesday, April 17, 2012 11:42 AM
To: '[email protected]'
Subject: [toad] RE: Get max length of data for a column


>> No. I want to know that in a varchar2(50) field that the max data is really 27 characters
I'm not sure Toad has a built in function to do what you ask.
With a little up-front effort, you could build a small procedure that would do that though.  Make sure you consider the datatype as well.  Blob columns and such are somewhat different to find out the max size as compared with the standard datatypes.
However - you probably want to avoid running such a procedure against a production database.  There could be a substantial hit to performance by querying a lot of large tables due to the fact there won't be indexes built for this kind of purpose so you're likely not to even make use of a single index and they'll all be full-table scans.
Additionally - you will want to be very careful using such a procedure against tables with a high number of columns (test well).  By its very nature, such a process will likely be dynamic: execute immediate.  Processing a dynamic statement inside a loop has - in my experience - a pretty high hit in performance due to the overhead involved in just handling the dynamic aspects.
More experienced individuals have indicated the older dynamic code is more flexible in that regard then "execute immediate" but I haven't the opportunity to really make use of that and find out.  I prefer to find another solution if I know the dynamic code will be looped more than a few times.
Roger S.

________________________________
This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.