x

Find the max length and resize the data type

Is there a way to find the maximum length of a value in a column? When I first created few tables I set the data length of varchar(1000). I didn’t have the length and data type information and I didn’t want the data to truncate during the load process. Since I’m finish loading all the files I want to resize the length and data type based on the max value of that column? Any idea how I can achieve this?

more ▼

asked Sep 20, 2013 at 04:04 PM in Default

avatar image

liton
910 35 41 49

The site works on voting. Every helpful answer can get a vote by clicking on the thumbs up next to those answers. If any one answer lead to a solution, click on the check mark next to that answer.

Sep 26, 2013 at 10:57 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Yes, you can find biggest Varchar using query below.

Select MAX(ColumnName) From (TableName)

And based on the results above, run query below to limit column to specific length.

Alter Table (TableName) Alter Column (ColumnName) Varchar(Max value from query above)

more ▼

answered Sep 20, 2013 at 04:20 PM

avatar image

eaglescout
720 5 8 12

I was looking for something that I could use to find the max length of all the columns at once instead of finding one columns at a time.

Sep 20, 2013 at 04:25 PM liton

Sorry @Liton, this is beyond my knoweledge base. I am sure there is a way to do what you want, but just know that the query will be complecated. Good Luck!

Sep 20, 2013 at 05:01 PM eaglescout
(comments are locked)
10|1200 characters needed characters left

You could possibly get some sql to generate this, but it's probably as quick to write by hand:

 select 
   max(len(column1)) as MaxLengthColumn1,
   max(len(column2)) as MaxLengthColumn2,
   ....
 from YourTable
more ▼

answered Sep 20, 2013 at 05:17 PM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

If I understand correctly, he is wanting to do this all in one statement like MAX(*), otherwise if he has 100 columns, it will take him all day to just write it out. For my own benefit @Kev Riley, is it possible to do this in one statement? Thanks!

Sep 20, 2013 at 05:23 PM eaglescout
(comments are locked)
10|1200 characters needed characters left

To get the max data length of all max data lengths in a table, you need to query each column individually. Then you need to get the max of those max(len))s.

This query will generate that query. It puts each column into its own max(len()) query then unions them all together to get the max(max(len()). It's set up to hit msdb.dbo.backupset so you can run the sample:

 USE msdb;
 SET NOCOUNT ON
 DECLARE @YourTableName sysname;
 SET @YourTableName = 'backupset'
 
 SELECT 'SELECT max(ColumnLength) FROM ('
 UNION ALL
 SELECT 'SELECT max(len([' + sc.name + '])) as ColumnLength FROM [' + t.name + '] UNION ALL' AS query FROM 
 sys.tables AS t
 INNER JOIN sys.columns AS sc ON t.object_id = sc.object_id
 INNER JOIN sys.types AS st ON sc.system_type_id = st.system_type_id
 WHERE 
 t.name = @YourTableName
 AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar')
 UNION ALL
 SELECT ') as src'

It's nearly the identical problem to the UPPER case each column from this previous question - http://ask.sqlservercentral.com/questions/106567/change-fields-to-uppercase.html#answer-106568

After you generate the query, just remove the final UNION ALL.

more ▼

answered Sep 20, 2013 at 06:41 PM

avatar image

KenJ
25k 3 10 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x11

asked: Sep 20, 2013 at 04:04 PM

Seen: 3786 times

Last Updated: Sep 26, 2013 at 10:57 AM

Copyright 2017 Redgate Software. Privacy Policy