RE: Get max length of data for a column

As one minor detail, you can get one grid with hundreds of rows instead of hundreds of grids by making the queries
select 'WT_SWIMS_FIELDWORK.FIELD_DESC', max(length(FIELD_DESC)) from WT_SWIMS_FIELDWORK UNION ALL
...
making it a single query with hundreds of unions.

Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. B2SB - Saint Louis, MO - 63167
314-694-2592

From: [email protected] [mailto:[email protected]] On Behalf Of Hudson, James - DNR
Sent: Tuesday, April 17, 2012 12:00 PM
To: [email protected]
Subject: [toad] 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 e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled
to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and
all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited.

All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto, including its
subsidiaries. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware".
Monsanto, along with its subsidiaries, accepts no liability for any damage caused by any such code transmitted by or accompanying
this e-mail or any attachment.


The information contained in this email may be subject to the export control laws and regulations of the United States, potentially
including but not limited to the Export Administration Regulations (EAR) and sanctions regulations issued by the U.S. Department of
Treasury, Office of Foreign Asset Controls (OFAC).  As a recipient of this information you are obligated to comply with all
applicable U.S. export laws and regulations.