With this script here, you can find all columns that don't match the database default:
DECLARE @DatabaseCollation VARCHAR(100)
SELECT
@DatabaseCollation = collation_name
FROM
sys.databases
WHERE
database_id = DB_ID()
SELECT
@DatabaseCollation 'Default database collation'
SELECT
t.Name 'Table Name',
c.name 'Col Name',
ty.name 'Type Name',
c.max_length,
c.collation_name,
c.is_nullable
FROM
sys.columns c
INNER JOIN
sys.tables t ON c.object_id = t.object_id
INNER JOIN
sys.types ty ON c.system_type_id = ty.system_type_id
WHERE
t.is_ms_shipped = 0
AND
c.collation_name <> @DatabaseCollation
For each of those columns, you can then execute this simple command to change their collation to be the database default:
ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(50) COLLATE DATABASE_DEFAULT
If you really really want, you could probably also combine the two - use a cursor on the SELECT
statement from the first script and then iterate over the results and execute the second statement for each column found - I typically prefer to do this manually, to be more aware of what I'm doing (and how much I'm changing):