This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.

Here’s the basic code:

USE master;  
GO
 
ALTER DATABASE Solutions  
COLLATE French_CI_AS ;  
GO 

This first changes to the master database, then we use the ALTER DATABASE statement to change the collation of the Solutions database to French_CI_AS collation.

Check the Collation

Here’s how to check the collation of your database:

SELECT DATABASEPROPERTYEX('Solutions','collation'); 

This returns a row with a single column that tells you what the database’s collation is.

You can run that to check the collation before you change the collation, and you can run it afterwards to ensure that the collation is exactly what you want.

Here’s another way to do it:

SELECT collation_name 
FROM sys.databases
WHERE name = 'Solutions';

You can omit the last line to return the collations of all databases.

Server Default Collation

Here’s how to return the server’s default collation:

EXECUTE sp_helpsort;

This stored procedure returns the default collation, as well as the sort order and character set for the instance of SQL Server.

Here’s what this stored procedure returned on my version of SQL Server 2017:

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Check the Available Collations

You can run the following statement to return a list of collations that are supported by your version of SQL Server:

SELECT 
  name,
  description 
FROM sys.fn_helpcollations();