Changing Collation of All Columns in SQL Server

Hello everyone. In this article, I will try to give information about changing the collation of all columns in SQL Server.

In SQL Server, in some cases, when you change the Collation of the database, the Collation may not change in the columns and you may want to change the Collation of all columns.

You can easily do this using the query below.

DECLARE @collate NVARCHAR(100);
DECLARE @table NVARCHAR(255);
DECLARE @column_name NVARCHAR(255);
DECLARE @column_id INT;
DECLARE @data_type NVARCHAR(255);
DECLARE @max_length INT;
DECLARE @row_id INT;
DECLARE @sql NVARCHAR(MAX);
DECLARE @sql_column NVARCHAR(MAX);

SET @collate = N'Turkish_CI_AI';

DECLARE local_table_cursor CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1;

OPEN local_table_cursor;
FETCH NEXT FROM local_table_cursor
INTO @table;

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE local_change_cursor CURSOR FOR
    SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id,
           c.name column_name,
           t.Name data_type,
           c.max_length,
           c.column_id
    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(@table)
    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;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        IF (@max_length = -1)
            SET @max_length = 4000;

        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql
                = N'ALTER TABLE ' + @table + N' ALTER COLUMN ' + @column_name + N' ' + @data_type + N'('
                  + CAST(@max_length AS NVARCHAR(100)) + N') COLLATE ' + @collate;
            PRINT @sql;
            EXEC sp_executesql @sql;
        END TRY
        BEGIN CATCH
            PRINT 'ERROR: Some index or contraint rely on the column' + @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;

    END;

    CLOSE local_change_cursor;
    DEALLOCATE local_change_cursor;

    FETCH NEXT FROM local_table_cursor
    INTO @table;

END;

CLOSE local_table_cursor;
DEALLOCATE local_table_cursor;

GO

Good luck to everyone in business and life.