How to Show the Collation of a Database in SQL Server (T-SQL)

Posted on by Ian

Here are two ways to return the collation of a database in SQL Server using Transact-SQL.

Query sys.databases

The first option is to run a query against sys.databases to return the collation of a specific database. The WHERE clause allows you to narrow the results down to the database/s you’re interested in:

SELECT 
    name, 
    collation_name 
FROM sys.databases
WHERE name = 'Music';

This results in something like this:

name   collation_name              
-----  ----------------------------
Music  SQL_Latin1_General_CP1_CI_AS

In this case, we specified the database called Music.

The DATABASEPROPERTYEX() Function

Another option is to use the DATABASEPROPERTYEX() function to return the default collation for a database:

SELECT DATABASEPROPERTYEX('Music', 'Collation') AS Collation;

Also see: