Remove collation from all database columns

I have a SQL Server database where the collation is set on it. However I am running into problems as the collation on some columns in the database varies from the database collation.

I would like to remove collation from the columns and use the collation set on the database.

I am working with SQL Server 2012.

How can I do this? Can a script do this?

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):

StackExchange Database Administrators Q#42027, answer score: 14