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 :)
