How To Change The Collation Of SQL Server Database, Tables Of Database and Columns Of Tables

dbtut

5 years ago

First of all, don’t forget to take backup for the security of your data.

You can change the database’s collation with the following code.

USE master
GO
SELECT DATABASEPROPERTYEX('test','COLLATION')
GO
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE test
GO   
ALTER DATABASE test COLLATE Turkish_CI_AI
GO
SELECT DATABASEPROPERTYEX('test','COLLATION')
GO
ALTER DATABASE test SET MULTI_USER

USE master

SELECT DATABASEPROPERTYEX('test','COLLATION')

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE test

ALTER DATABASE test COLLATE Turkish_CI_AI

SELECT DATABASEPROPERTYEX('test','COLLATION')

ALTER DATABASE test SET MULTI_USER

However, the database collation change does not apply to existing tables and columns.

If you want to make the same change for existing tables and all columns, you should run the following script after running the above script.

DECLARE @NewCollation sysname
set @NewCollation = 'Turkish_CI_AS'
select
    'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(stable.schema_id)) + '.' + QUOTENAME(stable.name) +
    ' ALTER COLUMN ' + QUOTENAME(scol.name) + ' ' + stype.name + '(' +
    CASE WHEN scol.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),scol.max_length) END +
    ') collate ' + @NewCollation + '
    go
    '
from sys.columns scol inner join sys.tables stable    on scol.object_id = stable.object_id
        inner join sys.types stype on scol.user_type_id = stype.user_type_id
where  scol.collation_name is not null and OBJECTPROPERTY(stable.object_id,N'IsMSShipped')=0

DECLARE @NewCollation sysname

set @NewCollation = 'Turkish_CI_AS'

select

'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(stable.schema_id)) + '.' + QUOTENAME(stable.name) +

' ALTER COLUMN ' + QUOTENAME(scol.name) + ' ' + stype.name + '(' +

CASE WHEN scol.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),scol.max_length) END +

') collate ' + @NewCollation + '

from sys.columns scol inner join sys.tables stable on scol.object_id = stable.object_id

inner join sys.types stype on scol.user_type_id = stype.user_type_id

where scol.collation_name is not null and OBJECTPROPERTY(stable.object_id,N'IsMSShipped')=0