Search code examples
sqlsql-servert-sqlcase-sensitive

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.

Solution

  • 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