SQL SERVER : How To Change Collation for all Table Columns.

Change Collation for all Tables columns: Here in this article will see how we can modify the collation of our database tables column. Our previous article has explained how we can find the collation of a database with a simple SQL Script in SQLSERVER. Here in this example, we set i.e alter collation of all tables columns as SQL_Latin1_General_CP1_CI_AS with the below-written query.  

The reason behind changing the collation of columns in SQL Server?

I was working with a SQL query, and this SQL query joined two tables from 2 different Database SERVER (SQL SERVER ). While executing that SQL query we got the below-written error

Cannot resolve the collation conflict between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS in the equal to operation.

So we came to know that both the SQL SERVER Database has different collation, i.e., each table, and each column has a different collation.
Also Read: How to find collation of database in SQL Server
Now to make that query run, we have to assure both the Database Servers must have the same collation.  And with the below-written query, we are able to alter the collation of all database columns.
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 = 'SQL_Latin1_General_CP1_CI_AS';

DECLARE local_table_cursor CURSOR FOR

SELECT [name]
FROM sysobjects

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table


    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


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

        IF (@data_type LIKE '%char%')
        BEGIN TRY
            SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
            PRINT @sql
            EXEC sp_executesql @sql
        END TRY
          PRINT 'ERROR: Some index or constraint 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


    CLOSE local_change_cursor
    DEALLOCATE local_change_cursor

    FETCH NEXT FROM local_table_cursor
    INTO @table

CLOSE local_table_cursor
DEALLOCATE local_table_cursor
Conclusion: Well, here 1st we create a cursor that gets all the table names, then we alter each table column collation. By this, we are able to change or modify collation for all the tables.

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to thank me? 👳 Buy Me a Coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.

Post Comment

Your email address will not be published. Required fields are marked *