Script to change collation of multiple columns in multiple table for all tables in database

Various time we come across situation where in lower environment the collation will be different and in production environment the collation will be different. This might hamper you work in many ways. Your development work may come to halt or slowdown making these changes.

So here is a small script to change collation of various columns for all table. If you want for some columns and some table the select statement in cursor needs to tweaked.

Declare 
@TableName varchar(300),
@ColumnName varchar(300),
@SQLText varchar(max),
@CharacterMaxLen varchar(max),
@CollationName varchar(max),
@IsNullable varchar(max),
@DataType varchar(max)

SET @CollationName = 'RequiredCollation'

Declare MyTableCursor cursor for
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR 
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' 
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position 
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + 
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + 
              ') COLLATE ' + @CollationName + ' ' + 
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
            PRINT @SQLText 

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor
--Print @SQLText
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor