-- Returns a list of executable ALTER sql statements for columns in a given db which don't match the database's default collation
-- To update the DB you need to run the returned SQL Statements...
-- ...but PLEASE REVIEW SQL GENERATED BEFORE EXECUTING

USE {YOUR DB HERE};

DECLARE @DatabaseCollation VARCHAR(100)
SET @DatabaseCollation = (SELECT collation_name FROM sys.databases WHERE database_id = DB_ID());

SELECT
	'ALTER TABLE ' + schema_name(t.schema_id)  + '.' + t.Name + ' ' + 
	'ALTER COLUMN ' + c.name + ' ' + 
	CASE WHEN ty.name = 'text'
		THEN ty.name + ' '
		ELSE ty.name + '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CAST(c.max_length as varchar) END + ') '
	END +
	'COLLATE DATABASE_DEFAULT ' +
	CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ' ' +
	';' AS SQLCommand
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 
	ty.name != 'sysname' AND
    c.collation_name <> @DatabaseCollation
ORDER BY t.Name, c.name

-- Background reading: 
-- * http://www.olcot.co.uk/sql-blogs/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as
-- * http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html#Appendix.SQLServer.CommonDBATasks.Collation
USE Master

SELECT NAME, COLLATION_NAME
FROM sys.Databases
ORDER BY DATABASE_ID ASC

-- Returns a list of any column which collation doesn't match the database's default

USE {YOUR DB HERE};

DECLARE @DatabaseCollation VARCHAR(100)

SET @DatabaseCollation = (SELECT collation_name FROM sys.databases WHERE database_id = DB_ID())

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
ORDER BY t.Name, c.name