SQL – Change column level Collation

Some times you may receive instructions from you DBA’s to change your database collation (which implies to propagate the change to the column level collation).

But the change at the database level does not propagate to your columns, to match all across by default and you are stuck to navigate to each table an find out the column collation and alter it to the desired.

So here I present a quick MS SQL script which dynamically generates the ALTER statements for the columns in the table to align the collation to the desired.

With only culprits left out, on which the index are built (requiring a drop and rebuild of the same)

DECLARE @collate SYSNAME
SELECT @collate = 'SQL_Latin1_General_CP1_CI_AS'

SELECT 
      '[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
    , 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
        ALTER COLUMN [' + c.name + '] ' +
        UPPER(t.name) + 
        CASE WHEN t.name NOT IN ('ntext', 'text') 
            THEN '(' + 
                CASE 
                    WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1 
                        THEN CAST(c.max_length / 2 AS VARCHAR(10))
                    WHEN t.name IN ('char', 'varchar') AND c.max_length != -1 
                        THEN CAST(c.max_length AS VARCHAR(10))
                    WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1 
                        THEN 'MAX'
                    ELSE CAST(c.max_length AS VARCHAR(10)) 
                END + ')' 
            ELSE '' 
        END + ' COLLATE ' + @collate + 
        CASE WHEN c.is_nullable = 1 
            THEN ' NULL'
            ELSE ' NOT NULL'
        END + ' 
		GO '
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
    AND c.collation_name != @collate
    AND o.[type] = 'U'

In the above SQL statement, the variable “@collate” defines the targeted collation which is desired for the columns.

And the ones in your database are not matching to the same will turn be provided with the respective SQL statement to effectively align the mismatch columns to the desired collation.

And if you are wondering, what collation relates to in MSSQL, please refer to the following MSDN Documentation, which gives a great insight on its importance.

Hope it helps!!!