Column collation to Server Default

The following script will help you to change all columns in your database to server default collation. You can modify this script to do all your databases on particular server.

set nocount on
declare @AlterId int
declare @serverColl varchar(100)
declare @AlterString varchar(300)
set @serverColl = convert(sysname, serverproperty('collation'))
declare @Testing table
(AlterId int identity(1,1) not null primary key clustered,
AlterString varchar(300))

INSERT INTO @Testing(AlterString)
select 'ALTER TABLE [' + so.name + '] ' +
'ALTER COLUMN [' + sc.name + '] ' +
st.name +
'(' + cast(sc.length as varchar) + ') ' +
'COLLATE '+ @serverColl + ''
from syscolumns sc
join sysobjects so
on so.id = sc.id
join systypes st
on st.xtype = sc.xtype
where sc.collation is not null
and OBJECTPROPERTY(Object_id(so.name), 'IsTable') = 1
and st.name!= 'sysname'
and sc.collation not like @serverColl
select @AlterId = Max(AlterId) from @Testing

WHILE @AlterId IS NOT NULL
BEGIN
SELECT @AlterString = AlterString FROM @Testing
WHERE AlterId = @AlterId
EXEC (@AlterString)
SELECT @AlterId = Max(AlterId) from @Testing
WHERE AlterId < @AlterId
END