How to change database collation?

Sometimes your server collation may not support for some application specific collation so you may need to change the database collation.  You can use  sys.databases   system cat log to check the database collations.  The below alter command show how to change database collation. One important thing is the database should be in single user mode so that during this time no other transaction can occur against this database. This method is applicable only for user database. For master database you need rebuild system database by using setup which will be demonstrated in other blog with more details.

SELECT name, collation_name

FROM sys.databases

USE master;

GO

ALTER DATABASE [DBA_Admin] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE [DBA_Admin]

COLLATE Latin1_General_CI_AS ;

GO

ALTER DATABASE [DBA_Admin] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

GO

SELECT name, collation_name

FROM sys.databases