Kyle Kyle - 6 months ago 16
SQL Question

SQL Server check case-sensitivity?

How can I check to see if a database in SQL Server is case-sensitive? I have previously been running the query:

SELECT CASE WHEN 'A' = 'a' THEN 'NOT CASE SENSITIVE' ELSE 'CASE SENSITIVE' END


But I am looking for other ways as this has actually given me issues in the past.

Edit - A little more info:
An existing product has many pre-written stored procedures. In a stored procedure
@test != @TEST
depending on the sensitivity of the server itself. So what I'm looking for is the best way to check the server for its sensitivity.

Answer

Collation can be set at various levels:

  1. Server
  2. Database
  3. Column

So you could have a Case Sensitive Column in a Case Insensitive database. I have not yet come across a situation where a business case could be made for case sensitivity of a single column of data, but I suppose there could be.

Check Server Collation

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

Check Column Collation

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name