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.
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
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.
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)