Alter the collation of all columns on a SQL Server database.

Convert_Collations.sql

DECLARE
    @collation_from VARCHAR(100),
    @collation_to VARCHAR(100);

-- Define the collations here
SET @collation_from = 'SQL_Latin1_General_CP1_CI_AS'
SET @collation_to = 'Latin1_General_CI_AS'



DECLARE 
    @sql varchar(max),
    @table varchar(max), 
    @column varchar(max),
    @is_nullable bit, 
    @column_type varchar(50),
    @max_length int,
    @max_length_text varchar(50);

DECLARE conflicts_cursor CURSOR FOR
    SELECT
        obj.name as 'table', 
        col.name as 'column',
        col.is_nullable,
        CASE col.system_type_id
        WHEN 167 THEN 'VARCHAR'
        WHEN 231 THEN 'NVARCHAR'
        WHEN 239 THEN 'NCHAR'
        END as 'column_type',
        col.max_length
    FROM 
        sys.columns col
    JOIN
        sys.objects obj ON obj.object_id = col.object_id
    WHERE
        col.collation_name = @collation_from
        AND col.system_type_id IN (
            167, -- VARCHAR
            231, -- NVARCHAR
            239  -- NCHAR
            )
        AND obj.type = 'U'

OPEN conflicts_cursor

FETCH NEXT FROM conflicts_cursor 
INTO @table, @column, @is_nullable, @column_type, @max_length;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF (OBJECT_ID(@table) IS NULL)
        PRINT @table + ' NOT EXIST'
    ELSE
    BEGIN
        IF (@max_length = -1 OR @max_length > 4000)
            SET @max_length_text = 'max'
        ELSE
            SET @max_length_text = CAST(@max_length AS varchar)

        SET @sql = CONCAT('ALTER TABLE [', @table, '] ALTER COLUMN [', @column, '] ', @column_type, '(', @max_length_text, ') COLLATE ', @collation_to)

        IF (@is_nullable = 1)
            SET @sql += ' NULL'
        ELSE
            SET @sql += ' NOT NULL'
    
        PRINT @sql
        EXEC(@sql)
    END

    FETCH NEXT FROM conflicts_cursor 
    INTO @table, @column, @is_nullable, @column_type, @max_length;
END

CLOSE conflicts_cursor;
DEALLOCATE conflicts_cursor;