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?
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)
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.` :