Change collations of all columns of all tables in SQL Server

Question

I imported a database with some data to compare with another database.

The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS.

I did change the collation of the source database in general to Latin1_General_CI_AS using the SQL Server Management Studio. But the tables and columns inside remains with the old collation.

I know that I can change a column using:

ALTER TABLE [table]  ALTER COLUMN [column] VARCHAR(100) COLLATE Latin1_General_CI_AS 

But I have to do this for all tables and all columns inside.

Before I know start to write a stored procedure that reads all tables and inside all column of type varchar and change them in a table and column cursor loop...

Does anyone know an easier way or is the only way to do this with a script running through all tables in a procedure?

Answer 1

As I did not find a proper way I wrote a script to do it and I'm sharing it here for those who need it. The script runs through all user tables and collects the columns. If the column type is any char type then it tries to convert it to the given collation.

Columns has to be index and constraint free for this to work.

If someone still has a better solution to this please post it!

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 = 'Latin1_General_CI_AS';  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) 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         BEGIN CATCH           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      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