SQL SERVER – How to Convert CollationID to Collation Name?

One of my blog readers sent email to me asking if there is a way to convert CollationID to Collation name. I replied asking more details about the requirement. Here is her reply.

Pinal,
I am in a trouble right now. Due to a hardware crash, I lost many of database files. Unfortunately, I don’t have backups, but I was able to retrieve MDF files. I was trying to follow your blog

SQL SERVER – FIX – Error: One or more files do not match the primary file of the database

I ran DBCC CHECKPRIMARYFILE and got CollationID. Now, I am planning to install SQL Server again and want to know the collation, which is equivalent to that number.

Thanks. Waiting for response.

I spent some time in searching the collation related functions in SQL Server. I was able to find below functions.

1. fn_helpcollations
2. COLLATIONPROPERTY
3. COLLATIONPROPERTYFROMID

Solution/Workaround: Convert CollationID to Collation Name

Here is the query which I came up with to map each CollationID to its collation name.

SELECT Collationproperty(NAME, 'CollationID') AS CollationID, 
       Collationpropertyfromid(CONVERT(INT, Collationproperty(NAME, 'CollationID')),'Name') AS 'CollationName' 
FROM   fn_helpcollations()

Here is the partial output

This query provided what she was needed and I was happy to help her.

Let me know what you think about this blog post in the comments section.

After You Know the Collation

Once you have the name, write it down before you install SQL Server again. Pick the same collation on the setup screen, because changing the server collation later is a lot more work than choosing it right the first time. After you attach the recovered files, run a quick check to confirm that the server and the database match:

SELECT SERVERPROPERTY('Collation') AS ServerCollation,
       DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;

If the two values differ, temporary tables use the server collation, and joins between them and your tables can fail with a collation conflict error. Checking this on day one saves you from a surprise weeks later.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.