Making collations uniform across multiple SQL Server 2008 databases
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 SQL_Latin1_General_CP1_CI_AS to 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 Latin1_General_100_CI_AS to Latin1_General_100_BIN2 or Latin1_General_100_CS_AS, or French_100_CI_AS, or even to SQL_Latin1_General_CP1_CI_AS.

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 CHAR / VARCHAR / 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. Latin1_General, French, Japanese, etc), then there shouldn't be any chance of that happening. And even going from Latin1_General to French isn't a problem as they both use Code Page 1252. But, going from Latin1_General to 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];


returns:

Latin1_General    French    Japanese
1252              1252      932


2 Almost all: collations of User-Defined Table Types are skipped.