How do I determine the collation of a database in SQL 2005?

Question

How do you determine the collation of a database in SQL 2005, for instance if you need to perform a case-insensitive search/replace?

Answer 1

Use the following SQL to determine the collation of a database:

SELECT DATABASEPROPERTYEX('{database name}', 'Collation') SQLCollation;

Answer 2

Remember, that individual columns can override the database collation:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS

Answer 3

If you want to do a case-insensitive search and can't rely on the database's collation, you could always specifically request it for the query you're interested in. For instance:
SELECT TOP 1 FName, *
FROM People
WHERE FName LIKE '%mich%' COLLATE Latin1_General_CI_AI


I usually have the opposite problem, where I want the case sensitivity but don't have it in the database's collation, so I find myself using the Latin1_General_BIN collation quite a bit in my queries. If you don't already know, you can do:
SELECT *
FROM ::fn_helpcollations()
for a list of the available collations and descriptions of what they're for.