List all supported SQL Server Collations and the Code Page of the Collation

To list all collations supported by the version of SQL Server that you are running, and the Non-Unicode code page of the collation, run the following query:

select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()

list_all_collations_with_code_page

Unicode-only collations do not have associated code pages, they are specified for Unicode-only data (nchar, nvarchar, and nvarchar(max)). They cannot be set at the database or server level.
If you try to create a database with a Unicode-only collation (for example 'Lao_100_CI_AI'), you will get the following error:

Collation 'Lao_100_CI_AI' is supported on Unicode data types only and cannot be set at the database or server level. (Microsoft SQL Server, Error: 453).

create_database_unicode_only_error

You can see the Code Page and Description for a specific collation:

select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
where name = 'Latin1_General_CS_AS'

collation_with_code_page_description

For example, you can list all collation that have a name that starts with 'SQL_L' and have a word 'insensitive' in the description:

select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()
where Name like 'SQL_L%' and Description like '%insensitive%';

collation_with_code_page_description_specific

If the post helped you, please share it:
Pin It

4 comments to List all supported SQL Server Collations and the Code Page of the Collation

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">