DECLARE @collation NVARCHAR(64)
SET @collation = 'Latin1_General_CI_AS'
SELECT
'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] '
+ 'ALTER COLUMN [' + COLUMN_NAME + '] '
+ DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH
WHEN -1 THEN 'MAX'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') '
+ 'COLLATE ' + @collation + ' '
+ CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
FROM INFORMATION_SCHEMA.columns
WHERE COLLATION_NAME IS NOT NULL
AND TABLE_NAME IN ( SELECT TABLE_NAME from information_schema.tables WHERE table_type = 'BASE TABLE' )
AND COLLATION_NAME <> @collation