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.