asked
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?
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.
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)
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!
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
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!
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][1] After you generate the query, just remove the final `UNION ALL.` [1]:
http://ask.sqlservercentral.com/questions/106567/change-fields-to-uppercase.html#answer-106568
18 People are following this question.
