How to Check Case-Sensitivity in SQL Server

 SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its "COLLATION" property and look for "CI" or "CS" in the result.

• CI = Case Insensitive
• CS = Case Sensitive

1. To check if a server is case sensitive, run this query:

   SELECT SERVERPROPERTY('COLLATION')

   A common result is:

   SQL_Latin1_General_CP1_CI_AS

   The CI indicates that the server is case insensitive.
2. To check if a specific SQL Server database is case sensitive, run this query:

   SELECT collation_name
   FROM sys.databases
   WHERE name = 'your_database_name'

   Again, this will output something like:

   SQL_Latin1_General_CP1_CI_AS

3. To check all databases on the server, just leave out the WHERE clause and include name in the SELECT list:

   SELECT name, collation_name
   FROM sys.databases

4. To check the collation for all columns in a SQL Server database table, run this query:

   SELECT COLUMN_NAME, COLLATION_NAME
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'your_table_name' AND CHARACTER_SET_NAME IS NOT NULL

Another command for checking the case-sensitivity and other properties of the database server is:

EXECUTE sp_helpsort

This will return something like:

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Finally, to see all collations supported by your SQL Server installation, run this:

SELECT name, description FROM sys.fn_helpcollations()

The result will be something like this: