sql-serverdynamic-sqlalter-column

Dynamic T-SQL - Alter column definition to max length of field


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)

Solution

  • 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)