How to find list of all collations available in a sql server database server

Sometimes, you may want to see the available collations in your sql server instance probably to find whether a database that you are going to restore with a certain collation, is available on your server.

The following query retrieves list of all the available collations in the sql server instance. This works only in SQL 2005 and above.

SELECT      *

FROM        fn_helpcollations()

This query can be executed from any database context on the server not necessarily only the master database as the result is the same.

How to find collation of a sql server instance

Say, you want to find the collation of your sql server instance. You could do it in two ways:

Through SSMS

In SSMS, connect to the server instance that you want to find the collation for.

Right click on the server instance node and select Properties.

It should open a “Server Properties” dialog.

On the General Page, you can see the Server Collation.

By using a Query

You can query for the collation as follows:

Connect to the sql server instance and open a new Query windows.

Issue the following select statement:

select SERVERPROPERTY('collation')

This query can be executed from any database context on the server not necessarily only the master database.