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.