Post reply

Generate script to change column length of string datatypes in a DB

  • December 26, 2011 at 10:13 pm

    #253934

    Comments posted to this topic are about the item Generate script to change column length of string datatypes in a DB

  • December 28, 2011 at 7:50 am

    #1426094

    Hey i liked your script, i made just a litle change to make it smaller, i hope you like too. There you go:

    DECLARE @SQLCode AS VARCHAR(MAX)

    DECLARE @TargetSize AS VARCHAR(50)

    SET @TargetSize = '600'

    SET @SQLCode = CAST (

    (SELECT 'alter table [' + OBJECT_NAME(c.OBJECT_ID) + '] alter column [' + c.name + '] '+t.name+' ('+@TargetSize+') ' + CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ';' as 'text()'

    FROM sys.columns AS c

    JOIN sys.types AS t ON c.user_type_id=t.user_type_id

    WHERE t.name like '%char' -- data type that has to be changed.

    and c.max_length > 400 -- size of the column can be changed.

    and OBJECT_NAME(c.OBJECT_ID) not like 'sys%' -- to avoid systables (This can also be used to avoid any configuration tables)

    for xml path('')

    )

    AS VARCHAR(MAX)

    )

    PRINT @SQLCode

    EXEC (@SQLCode)

  • December 28, 2011 at 10:30 am

    #1426182

    It is good - Thanks

  • May 11, 2016 at 11:46 am

    #1878346

    Thanks for the script.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply