How to Check Case-Sensitivity in SQL Server

SELECT SERVERPROPERTY('COLLATION')

SELECT name, collation_name
FROM sys.databases

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()

Sources:

https://www.webucator.com/article/how-to-check-case-sensitivity-in-sql-server/