2011-02-24

List of collations

I would like to see a list of available collations on a given SQL Server database installation, and a combination of fn_helpcollations() and COLLATIONPROPERTY() gives a quick answer.
SELECT
 [name] 
 ,COLLATIONPROPERTY([name], 'CodePage') AS [CodePage]
 ,COLLATIONPROPERTY([name], 'LCID') AS [LCID]
 ,COLLATIONPROPERTY([name], 'ComparisonStyle') AS [ComparisonStyle]
 ,COLLATIONPROPERTY([name], 'Version') AS [Version]
 ,[description]
FROM ::fn_helpcollations();
On a SQL Server 2008 R2 (10.50.1600) I get 2397 collations.
I do miss the sort order on the SQL collation as the table in "Selecting a SQL Server Collation".

A search for collation objects points at the stored procedures sys.sp_tablecollations, that uses the view sys.spt_tablecollations_view. It looks like this view is placed in the database "mssqlsystemresource", but this I cannot access direct.

Reference

MSDN Library: "Selecting a SQL Server Collation".
MSDN Library: "Collation and Unicode Support".
Alexander Barkov: "MS SQL Server collation charts".
The Unicode Consortium: "Unicode Collation charts".
G-Productions: "sys.sp_tablecollations".

No comments: