Change Collation for all objects in a database

Declare

    @NewCollation varchar(255), @DBName sysname

Select  @NewCollation = 'SQL_Latin1_General_CP1_CI_AS', -- change this to the collation that you need

        @DBName = DB_NAME()

Declare

    @CName varchar(255), @TbleName sysname, @objOwner sysname, @Sql varchar(8000), @Size int, @Status tinyint, @Colorder int

Declare CurWhileLoop cursor read_only forward_only local

for Select

       QUOTENAME(C.Name)

      ,T.Name

      ,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)

      ,C.Prec

      ,C.isnullable

      ,C.colorder

    From syscolumns C

      inner join systypes T on C.xtype=T.xtype

      inner join sysobjects O on C.ID=O.ID

      inner join sysusers u on O.uid = u.uid

    where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')

      and O.xtype in ('U')

      and C.collation != @NewCollation

    and objectProperty(O.ID, 'ismsshipped')=0

    order by 3, 1

open CurWhileLoop

SET XACT_ABORT ON

begin tran

fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder

while @@FETCH_STATUS =0

begin

  set @Sql='ALTER TABLE '+@objOwner+' ALTER COLUMN '+@CName+' '+@TbleName+ isnull ('('

+convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation

+' '+case when @Status=1 then 'NULL' else 'NOT NULL' end

  exec(@Sql) -- change this to print if you need only the script, not the action

  fetch CurWhileLoop into @CName, @TbleName, @objOwner, @Size, @Status, @Colorder

end

close CurWhileLoop

deallocate CurWhileLoop

commit tran



Source: http://stackoverflow.com/questions/3655304/changing-sql-server-database-collation