Change collation of table columns
In the MS SQL SERVER, the collation can be set at the column level. If the fields, in which tables are joined, have different collation, the result would be a error “Cannot resolve collation conflict ...“.
To resolve the collation conflict you may add keywords COLLATE DATABASE_DEFAULT around = operator
But sometimes you have to do the same collation in all fields of the database.
It turns into a big problem, becouse you cannot change the collation of a column that is currently referenced by any one of the following:
- A computed column
- An index
- Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
- A CHECK constraint
- A FOREIGN KEY constraint
You can use our function Change columns collation to safety change collation of columns.
To invoke the Change columns collation:
- Right click on the selected object and then choose SQL Refactor Studio -> Change columns collation… menu command.
- Select the new collation from the drop-down list and press the Apply button.
- Choose columns in which you want to change collation and press the Generate change script button.
- Review and execute the script (press F5)