:::: MENU ::::

Get list of all SQL Server collations

  • Mar 21 / 2009
  • 4
Data Modeling and Database Design, dbDigger, SQL Server Collations, System Functions

Get list of all SQL Server collations

Various collations are available in all versions of SQL Server. According to BOL A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages). SQL Server collation may effect sort order for unicode, non-unicode data types and code page used for storing non-unicode character data types. In order to get list of all available collations of SQL Server use following script

 
SELECT *  
 FROM ::fn_helpcollations()  
 GO  

In case of SQL Server 2005 and onwards function prefix :: may be removed. Hence script for collation list in SQL Server 2005 and onwards will be

SELECT *  
 FROM fn_helpcollations()  
 GO  
  • Anonymous

    What about the following type of collation,(error message below)
    Msg 468, Level 16, State 9, Line 1
    Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

    regards
    Ahmed Faraz

  • Atif Shehzad

    The reason for this error may be that collation of your working database and default collation on your instance does not match. To resolve this issue you have to use COLLATE key word for mentioning database collation in the script. Please search BOL for use of COLLATE key word.

  • Anonymous

    Does your script need to define 'USE DBNameHere'.

    Whatif just use this:

    SELECT *
    FROM fn_helpcollations()
    GO

  • Atif Shehzad

    You are right, as this is server level data so no need to specify any specific database here. I have modified the script. Thanks for pointing it out.

Consult us to explore the Databases. Contact us