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

  • cusvenus

    Mr or Mrs. 500

    Points: 525

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

  • adrian.facio

    SSCrazy

    Points: 2405

    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)

  • cusvenus

    Mr or Mrs. 500

    Points: 525

    It is good - Thanks

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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