Have you ever encountered the frustrating “CANNOT RESOLVE COLLATION” message when trying to join data with different collation settings in SQL Server? Or perhaps you’ve realized that your database collations have become a mess due to various reasons. Whatever the case may be, manually consolidating collations can be a nightmare.

In this article, we will discuss a solution to this problem using a set of scripts created by Brian Cidern. These scripts are designed to identify columns with incorrect collation values, script out all related objects, drop and recreate them with the correct collation, and ultimately consolidate collations in your database.

The Basic Approach

Here is the basic approach outlined by Brian:

1. Identify all the columns which have incorrect collation values.
2. Identify all objects bound to the above columns, including indexes, keys, checks, and user-defined functions.
3. Script out all of the above object DDL.
4. Drop all of the objects.
5. Update all the columns with the correct collation.
6. Recreate all of the dropped objects using the generated DDL.

These steps are performed using a collection of non-destructive scripts that generate the necessary DDL statements. It is important to save these generated scripts for later use on the target databases.

Script 1 – The DDL Generator

The first script creates a stored procedure that will be repeatedly called. This procedure uses DMO via OLE to capture a buffer dump of the object code, similar to right-clicking an object and selecting “Script to new window as…”. The subsequent scripts will automatically perform the necessary steps, and this script will be called repeatedly.

Script 2 – Drop Dependencies

This script generates a collection of DROP statements for objects that are bound to columns with incorrect collation settings. The objects are concatenated together using UNIONs in a specific order to avoid dependency issues. The resulting collection of DROP statements must be saved for later use.

Script 3 – Change Collations

Similar to script 2, this script generates a collection of DDL statements to update the column collations. It is important to edit this script to use your desired collation by performing a search and replace of the current collation with your chosen collation. The generated output should be saved for later use.

Script 4 – Create Dependent Objects

This script is where most of the work takes place. It generates a series of DDL statements to recreate the dropped objects with the correct collation. It is important to edit this script to use your desired collation by performing a search and replace of the current collation with your chosen collation. The output of this script needs to be copied and pasted into a new query window and executed.

Final Step

After executing the above scripts, you will have three scripts that need to be run against the target database. It is crucial to note that these generated scripts are very destructive, so thorough testing is recommended before executing them on a production database. Each scenario may require modifications to these scripts, as they were designed to fix specific needs but kept as generic as possible for re-use.

By following these steps, you will be able to consolidate collations in your SQL Server database, ensuring that each column and the database itself are set to your desired collation setting.

Good luck with your collation consolidation journey!

Author: Brian Cidern

Email: brians.sql.blog@gmail.com