Change MSSQL column collation - is there anything I can do to make this easy?
Posted on 2016-07-25
I'm in a bind with our application upgrade. It's failing due to collation conflicts in the database. I analyzed our MSSQL collation properties by instance>database>columns and uncovered a mess.
SQL 2014 instance: "SQL_Latin1_General_CP1_CI_AS"
Application database: "Latin1_General_CI_AS"
(SQL instance is dedicated to the application database. No other db's exist besides system db's)
I confirmed column level collation by running the following:
select sc.name column_name, sc.collation_name column_collation, so.name table_name from sys.columns sc, sys.objects so where sc.object_id = so.object_id and sc.object_id > 2000 and sc.collation_name IS NOT NULL order by table_name, column_name
In the database;
800 columns with "SQL_Latin1_General_CP1_CI_AS"
240 columns with "Latin1_General_CI_AS"
From a high level I believe the most efficient approach would be correcting collation on the 240 tables to match the SQL instance -AND- change the database collation. End result is everything will be "SQL_Latin1_General_CP1_CI_AS".
I believe the process goes..export data > drop the columns associated objects (indexes, triggers, etc) > drop the column > recreate column with proper collation, recreate objects, reimport data.
1. Are there any queries I can run to understand what objects are associated/dependent on these columns? That would help me understand how many changes are required and where.
2. Should I change the database collation before or after correcting the mismatched columns?
Thanks for any tips/tricks you can provide.