declare @OldCollName varchar(200)
	,@NewCollName varchar(200)

Select	@NewCollName = 'SQL_1Xcompat_CP850_CI_AS'
	,@OldCollName = 'SQL_Latin1_General_CP1_CI_AS'

select	'Alter Table [' + t.Table_Schema + '].[' + c.Table_Name + ']' +
	' Alter Column [' + c.Column_Name + '] ' + 
	Data_Type + 
	case 
	when data_type like '%char' OR data_type like '%binary' then '(' + convert(varchar,character_maximum_length) + ')'
	when data_type IN ('numeric', 'decimal') then '(' + convert(varchar,numeric_precision) + ',' + convert(varchar,numeric_scale ) + ')'
	else '' 
	end + 
	case when c.data_type like '%char' OR c.data_type like '%text' then ' COLLATE ' + @NewCollName  else '' end +
	case when Is_Nullable = 'Yes' then ' NULL ' else ' NOT NULL ' end
from	information_schema.columns c
JOIN	information_schema.tables t ON c.table_name = t.table_name
where	t.Table_Type = 'Base Table'

--ONLY Columns that don't have your desired Collation
--AND	c.Collation_Name <> @NewCollName

--Only columns that have a spicific Collation
AND	c.Collation_Name = @OldCollName

--can't alter text columns
AND	data_type not IN ('text', 'ntext', 'image')

Order by c.Table_Name
	,c.ordinal_position