SQL Server Collations Find current collation and all supported collations — Parv The IT Geek

Have you ever needed to check your database or SQL instance to find out its collation? Have you ever wanted to check whether a collation is supported by Windows or SQL Server?

Well I am going to show you how to find out the information quickly and easily.

The below query will show the instance level collation.

The query below will show the collation setting at the database level

SELECT DATABASEPROPERTYEX(‘AdventureWorks2012’, ‘Collation’) SQLCollation

Sometimes its important to know the collations which the OS supports and the following query will help list all the collations which are supported by the OS.

SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE ‘SQL%’;

When doing migrations it is very important to ensure that the Collation setting is maintained and to know whether it is supported by the SQL instance. The below query will help you easily and quickly ascertain that information.

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE ‘SQL%’;

Hopefully that covers all your collation gathering requirements but if you do have another technique which I have missed then please leave them in the comments below.

Originally published at https://parvtheitgeek.com on April 21, 2015.


Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: