Update Collation SQL Server

Still talking about implementing foreign characters to your application. in SQL Server database we know what so-called Collation. you need to set the collation of the database and the table’s column to collation type that support to keep the foreign characters into the table. what I’m going to show here, is a solution for a problem that you may have when you want to alter table collation in a situation where there’s numerous number of tables to alter. hope this below SQL script might help you

 declare   
   @NewCollation varchar(255)  
   ,@Stmt nvarchar(4000)  
   ,@DBName sysname  
 set @NewCollation = 'Thai_CI_AS' -- change this to the collation that you want to use  
 set @DBName = DB_NAME()  
 declare  
   @CName varchar(255)  
   ,@TName sysname  
   ,@OName sysname  
   ,@Sql varchar(8000)  
   ,@Size int  
   ,@Status tinyint  
   ,@Colorder int   
 declare curcolumns 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 curcolumns  
 SET XACT_ABORT ON  
 fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder  
 while @@FETCH_STATUS =0  
 begin  
      set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName+ isnull ('('  
           +convert(varchar,@Size)+')', '') +' COLLATE '+ @NewCollation  
           +' '+ case when @Status=1 then 'NULL' else 'NOT NULL' end  
      begin try  
      exec(@Sql)  
      end try  
      begin catch  
      --error catch  
           set   
           print error_message()    
      end catch  
      fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder  
 end  
 close curcolumns  
 deallocate curcolumns  

you can run the script in SQL server 2005 or above, well, that’s a little trick for me, hope can help.

Thanks to Kim that had helped me on this :)