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

• cusvenus

  Mr or Mrs. 500

  Points: 525

  More actions

  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

• adrian.facio

  SSCrazy

  Points: 2406

  More actions

  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)

• cusvenus

  Mr or Mrs. 500

  Points: 525

  More actions

  December 28, 2011 at 10:30 am

  #1426182

  It is good - Thanks

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 11, 2016 at 11:46 am

  #1878346

  Thanks for the script.