How change the database collation settings Latin1_General_CI_AS_KS_WS to another one.

Question

How change the database collation  settings Latin1_General_CI_AS_KS_WS to another one.

Answer 1

Hi RoyaIM,

Follow these steps to change DB collation type;

1) Right click Database name to enter Properties.

2) In the Options tab, change collation type to another.

Or you can change it with T-SQL;

-- Usage:
ALTER DATABASE Database_name COLLATE New_Collation_Name

-- Example: 
ALTER DATABASE AdventureWorks COLLATE SQL_Latin1_General_CP1_CI_AS

Best Regards,

Yigit.

---

http://www.yigitaktan.com

Answer 2

Hello,

With the command ALTER DATABASE, see MSDN ALTER DATABASE => B. Changing the collation of a database

But this will change only the database collation, not the collation of existing tables.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 3

Note that only new data inserted into will be  under new collation..

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 4

And also,

If you want to look all of column collation execute this;

USE Database_Name
GO

SELECT QUOTENAME(b.name) + '.' + QUOTENAME(c.name) + '.' + QUOTENAME(d.name) AS [Database.Schema.Table], d.collation_name AS Collation 
FROM sys.schemas b
   INNER JOIN sys.tables c
           ON c.schema_id = b.schema_id
   INNER JOIN sys.columns d
           ON d.object_id = c.object_id

Best regards,

Yigit.

---

http://www.yigitaktan.com

Answer 5

if there is any script to update collation settings schema wise and database wise please provide.

Answer 6

This script change all database (without system databases) collation settings with a new one.

DECLARE @DB varchar(250)
DECLARE @New_Collation varchar(250)
SET @New_Collation = 'SQL_Latin1_General_CP1_CI_AS'
DECLARE DB_cursor CURSOR FOR

SELECT name FROM SYS.DATABASES WHERE database_id NOT IN (1,2,3,4)

OPEN DB_cursor
FETCH NEXT FROM DB_cursor
INTO @DB

WHILE @@FETCH_STATUS = 0
BEGIN

ALTER DATABASE @DB COLLATE @New_Collation

   FETCH NEXT FROM DB_cursor
   INTO @DB
END

CLOSE DB_cursor
DEALLOCATE

Best regards,

Yigit.

---

http://www.yigitaktan.com

Answer 7

thanks for your help.

i have solve the issue according to your steps.