what would be a good approach for making [collations for columns] all uniform?
Well, "good" is highly subjective. The first thing to consider is the scope of what you believe should be changed. How many databases are affected? How many columns in each database would be changed? And how many total string columns are there? Does the collation need to change at the database level for any of the databases? Does the collation need to change at the server level? Does the issue you are trying to fix revolve around column-to-column comparisons / joins, or is it column-to-variable / literal comparisons, or both?
If most columns have the same collation but a small percentage have different collations, was that intentional such that changing the collation would change the behavior and/or performance of some queries? Meaning, it is one thing to want to change
Latin1_General_100_CI_AS as that is at least the same code page, culture (i.e. linguistic rules), and sensitivities (accent-sensitive, case + kana type + width insensitive). But it is quite another to change
French_100_CI_AS, or even to
If it is a small number / percentage of columns to change, AND assuming that the "incorrect" collations were not intentional and purposeful, then you should probably change them one at a time via
ALTER TABLE ... ALTER COLUMN. You might need to drop and recreate dependencies if any exist, such as FKs, indexes, functions referencing those columns, etc.
If it is a lot of columns, and even more so if there is a need to change database and/or instance collations, then you could look into the undocumented approach that I documented here:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
There are pros and cons to that "undocumented" approach, but it does greatly reduce the workload of making the changes as it side-steps the dependency issues. There are a few gotchas that will affect some folks and not others, but only one that can't be fixed easily enough: changing code pages on a
TEXT column1. However, I am guessing that it is fairly rare that someone would be changing collations that also change the code page (but if that is the case, then you can't use this undocumented approach without first making sure that you aren't using any of the characters that would be changing). Still, it changes ALL2 collations, which is generally desired in these cases, but does require that you might need to change a few back manually if there are some non-standard collations being used that are there for a good reason.
1 Code page change happens when the code page used by the old collation is different than the code page used by the new collation. If the old and new collations are in the same culture / locale (i.e.
Japanese, etc), then there shouldn't be any chance of that happening. And even going from
French isn't a problem as they both use Code Page 1252. But, going from
Japanese is a problem as they use different code pages. You can determine the code page using the
COLLATIONPROPERTY built-in function. For example:
SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS', 'CodePage') AS [Latin1_General],
COLLATIONPROPERTY(N'French_100_CI_AS', 'CodePage') AS [French],
COLLATIONPROPERTY(N'Japanese_CI_AS', 'CodePage') AS [Japanese];
Latin1_General French Japanese
1252 1252 932
2 Almost all: collations of User-Defined Table Types are skipped.