RE: Get max length of data for a column

You also can look at the stats table in the data dictionary - I know it has average column size - it might also have max one ....


From: [email protected] [mailto:[email protected]] On Behalf Of Tryon, Christopher S
Sent: Tuesday, April 17, 2012 11:48 AM
To: [email protected]
Subject: [toad] RE: Get max length of data for a column


Thanks for the advice. I was thinking about doing that. Typically this happens very infrequently. Mostly when analyzing data for interfaces between systems. Some DBA's create large fields so they don't mess with them in the future.

Chris

From: [email protected]<mailto:[email protected]> [mailto:[email protected]]<mailto:[mailto:[email protected]]> On Behalf Of Simoneau, Roger
Sent: Tuesday, April 17, 2012 12:42 PM
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.

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

______________________________________________________________________
Attention:
This e-mail and any files transmitted with it from Hatch Mott MacDonald are confidential and intended solely for use of the individual or entity to whom they are addressed. If you have received this e-mail in error please immediately notify the sender.
______________________________________________________________________