Another Way to Changing String Columns Collation

Let's say you have a database table, which contains millions rows of string data. Someday you need to change the collation, for example originally it's case sensitive, now you want to change it to case insensitive. According to SQL Server BOL, you can change the column collation, but first you need to drop all the reference objects referring to that column, including computed column, indexes, statistics, CHECK constraints, and FOREIGN KEY constraints. It's not a simple task, and if you do this way in your production database, you need a prolonged maintenance period. In this blogpost, I want to show you another way to do this, but it needs a schema comparison tool and a data comparison tool, such as Red Gate® SQL Compare and Data Compare.

1. Create a new database as the destination database, specify the new collation.
2. From the Source database, generate the creation script for all Tables, clear all options EXCEPT "Schema qualify object names". In this step, you just need to create the table, without any index nor constraints.
3. Change the current query context database to the destination database, execute the table creation script.
4. Generate the data copy script (INSERT…SELECT statements) for all tables, using below script:
   USE <source DB>GO
   DECLARE @destDb varchar(50) = '<destination DB>';
   SELECT CASE WHEN I.column_id IS NULL THEN '' ELSE 'SET IDENTITY_INSERT ' + @destDb + '.' + S.name + '.' + O.name + ' ON; ' END + 'INSERT ' + @destDb + '.' + S.name + '.' + O.name + ' (' + RTRIM(LTRIM(STUFF((SELECT ', [' + name + ']' FROM sys.columns WHERE object_id = O.object_id FOR XML PATH ('')), 1, 1, ''))) + ') SELECT ' + RTRIM(LTRIM(STUFF((SELECT ', [' + name + ']' FROM sys.columns WHERE object_id = O.object_id FOR XML PATH ('')), 1, 1, ''))) + ' FROM ' + S.name + '.' + O.name + '; ' + CASE WHEN I.column_id IS NULL THEN '' ELSE 'SET IDENTITY_INSERT ' + @destDb + '.' + S.name + '.' + O.name + ' OFF; ' END FROM sys.objects O JOIN sys.schemas S ON O.schema_id = S.schema_id LEFT JOIN sys.identity_columns I ON I.object_id = O.object_id WHERE O.[type] = 'U' ORDER BY S.name, O.name;
5. Execute generated the INSERT…SELECT statements.
6. Backup the Destination database.
7. Run Red Gate® SQL Compare to synchronize table schema (PK, FK, check, index, etc.) and objects (view, stored proc, function, etc.) from source database to destination database.
8. Freeze the source database, e.g. ALTER DATABASE <source DB> SET READ_ONLY;
9. Run Red Gate® SQL Data Compare to compare and synchronize the source database and destination database.
10. Rename the source database to XXX_OLD, and destination database to the source database original name.