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

Here is the query which I came up with.

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)