I am trying to dynamically create code to alter the column definition to the max length of the field.
Note that the contents of the database will not change.
Here is what I have so far, but I cannot separately execute the max length query to get a number. Where am I going wrong?
Regards.
DECLARE @SQL_STMT VARCHAR(MAX) = ''
SELECT @SQL_STMT = @SQL_STMT
+ '''ALTER TABLE '
+ TABLE_NAME
+ ' ALTER COLUMN '
+ COLUMN_NAME
+ ' '
+ DATA_TYPE
+ '('' SELECT MAX(LEN('
+ COLUMN_NAME
+ ')) FROM '
+ TABLE_NAME
+ ''') ''
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'
PRINT(@SQL_STMT)
EDIT My first version was not refined enough, here is a new version that I have tested :
DECLARE @SQL_STMT VARCHAR(MAX) = 'DECLARE @query nvarchar(max);'
SELECT @SQL_STMT = @SQL_STMT
+ 'SET @query =''ALTER TABLE '
+ TABLE_NAME
+ ' ALTER COLUMN '
+ COLUMN_NAME
+ ' '
+ DATA_TYPE
+ '('' +CAST((SELECT MAX(DATALENGTH('
+ COLUMN_NAME
+ ')) FROM '
+ TABLE_NAME
+ ') as nvarchar(max))+'') ''
exec(@query);'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'nvarchar'
PRINT(@SQL_STMT)