The following code will update a column to varchar(max) only, when it does not already have the varchar(max) length.

-- Update column 'Name' length, it should be varchar(max).
-- Note: varchar(max) has length '-1'.
if (    
            select character_maximum_length
            from information_schema.columns
            where table_name=object_name(object_id('dbo.Table1'))
            and COLUMN_NAME = 'Name'
        ) <> -1
    alter table dbo.Table1 alter column Name varchar(max) not null

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.