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.
SELECT SERVERPROPERTY(‘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.
Parv The IT Geek 