How to change database or server collation

How to change database collation

To change the database collation you can use alter database operator. For instance to change collation of previously created database collationtest you can run the script

alter database collationtest collate Cyrillic_General_CI_AS

Script runs for a second and does not require the database to not have active connections. this does not change collation of the objects that have already been created. It only changes default database collation which means if you create a table with character columns in the future and you don’t explicitly specify its collation, the collation will be Cyrillic_General_CI_AS (database_default). So our problem query still fails with the same error. So what we need to do? Yes, change collation of all exisiting objects(columns) in the database. Every single column collated with previous default database collation must be altered. The following script does exactly this

declare

@NewCollation varchar(255)

,@Stmt nvarchar(4000)

,@DBName sysname

set @NewCollation = ‘Cyrillic_General_CI_AS’ — change this to the collation that you need

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

begin tran

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

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

fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder

end

close curcolumns

deallocate curcolumns

commit tran

You will also need to disable replication if affected columns consist in published articles.

If your character columns are primary keys for some tables and foreign keys in others then situation gets worse. You need to drop the foreign keys , drop primary keys, change the collation and recreate the PKs and FKs.

In the end you will need to refresh the views.

declare @ViewName varchar(255), @Sql varchar(8000)

declare curviews cursor read_only forward_only local

for Select QUOTENAME(U.Name) + ‘.’ +QUOTENAME(O.Name)

from sysobjects O

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

Where O.xtype=’V’

open curviews

fetch curviews into @ViewName

while @@FETCH_STATUS =0

begin

Set @Sql = ‘exec sp_RefreshView “‘ + @ViewName + “"

exec (@Sql)

fetch curviews into @ViewName

end

close curviews

deallocate curviews