Satheesh Kannan Chandra's Blog

To change collation type for all the character type fields to a new collation (other than editing manually each table in 'Design Table' through EM) . The following scrtipt worked fine for me.

This script has some problem while changing column with constrainsts / indexes. So can make another scripts for droping and recreating constrainsts / indexes. Execute those constraint scripts before and after the collation change.

declare @collation varchar(50)
set @collation = 'SQL_Latin1_General_CP1_CI_AI '

select 'alter table [' + sysusers.name + '].[' + sysobjects.name + '] ' +
'alter column [' + syscolumns.name + '] ' +
systypes.name +
case
when systypes.name = 'nvarchar' or systypes.name = 'nchar'
then '(' + cast(syscolumns.length / 2 as varchar) + ') '
else '(' + cast(syscolumns.length as varchar) + ') '
end +
' collate ' + @collation +
case when syscolumns.isnullable = 1 then 'NULL' else 'NOT NULL' end sql
from syscolumns
inner join sysobjects
on sysobjects.id = syscolumns.id
inner join systypes
on systypes.xtype = syscolumns.xtype
inner join sysusers
on sysusers.uid = sysobjects.uid
where syscolumns.collation is not null
and sysobjects.xtype = 'U'
and systypes.name <> 'sysname'
and syscolumns.type = 39