Change the collation of SQL Server database columns

change_column_collation.sql

DECLARE @collate nvarchar(100);
DECLARE @schema nvarchar(255);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length nvarchar(255);
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @nullable bit;
DECLARE @null nvarchar(25);

SET @collate = 'SQL_Latin1_General_CP1_CI_AS';

DECLARE local_table_cursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 
SELECT	s.[name] AS [schema],
		o.[name] AS [table]
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE OBJECTPROPERTY(o.object_id, N'IsUserTable') = 1
ORDER BY [schema],[table]

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @schema, @table

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR 

    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
        , c.name column_name
        , t.Name data_type
        , CASE
			WHEN (c.max_length = -1) THEN 'max'
			WHEN (c.max_length > 4000) THEN 'max'
			ELSE CAST(c.max_length AS nvarchar(100))
		  END
        , c.column_id
		, c.is_nullable
    FROM sys.columns c
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    WHERE c.object_id = OBJECT_ID(@schema + '.' + @table)
	AND c.collation_name <> @collate
    ORDER BY c.column_id

    OPEN local_change_cursor
    FETCH NEXT FROM local_change_cursor
    INTO @row_id, @column_name, @data_type, @max_length, @column_id, @nullable

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @null=' NOT NULL'
        IF (@nullable = 1) SET @null=' NULL'
        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE [' + @schema + '].[' + @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @max_length + ') COLLATE ' + @collate + @null
            PRINT @sql
            EXEC sp_executesql @sql
        END TRY
        BEGIN CATCH
          PRINT 'ERROR: Some index or constraint rely on the column [' + @schema + '].[' + @table + '].[' + @column_name + '], No conversion possible'
          PRINT @sql
        END CATCH

        FETCH NEXT FROM local_change_cursor
        INTO @row_id, @column_name, @data_type, @max_length, @column_id, @nullable
    END

    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
	INTO @schema, @table

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO