Find columns with different collation

use [<DB_NAME>]

GO

DECLARE @NewCollation sysname
SET @NewCollation = 'Latin1_General_CI_AI' -- or any other Collation....
SELECT
T.TABLE_NAME, C.COLUMN_NAME, T.TABLE_SCHEMA, C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH, C.IS_NULLABLE, C.COLLATION_NAME,
-- alter collation statement
'ALTER TABLE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME +
' ALTER COLUMN ' + C.COLUMN_NAME+ ' ' + C.DATA_TYPE +
CASE WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)'  
WHEN C.DATA_TYPE IN ('text', 'ntext') THEN ''  
ELSE '(' + CAST (C.CHARACTER_MAXIMUM_LENGTH AS nvarchar(10)) + ')'  
END +
' COLLATE ' + @NewCollation + ' ' + 
CASE C.IS_NULLABLE WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END 
FROM INFORMATION_SCHEMA.COLUMNS C 
JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME 
WHERE C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext', 'sysname') 
AND C.COLLATION_NAME <> @NewCollation 
AND T.TABLE_TYPE = 'BASE TABLE' 
order by T.TABLE_NAME