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

SQL SERVER - How to Convert CollationID to Collation Name? collationID-Name

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)

, ,
Previous Post
SQL SERVER – Find Out Column Name Using COL_NAME() Function
Next Post
SQL SERVER – How to Build Three Part Name from Object_ID – Part 2?

Related Posts

Leave a Reply

Menu