MS SQL Collation (case sensitive)

SQL server determines case sensitivity by COLLATION.

COLLATION can be set at various levels.

1. Server-level
2. Database-level
3. Column-level
4. Expression-level

Here is the MSDN reference.

One can check the COLLATION at each level as mentioned in Raj More's answer.

Check Server Collation

SELECT SERVERPROPERTY('COLLATION')

Check Database Collation

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

Check Column Collation

declare @table_name varchar(50)
set @table_name = 'order'
select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

Check Expression Collation

For expression level COLLATION you need to look at the expression. :)

It would be generally at the end of the expression as in below example.

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

COLLATE Latin1_General_CS_AI COLLATE SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:
ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE Latin1_General_CS_AS 
GO 



SELECT mycolumn FROM mytable WHERE mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE mycolumn='caSE' 
SELECT mycolumn FROM mytable WHERE mycolumn='case' 

If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):
ALTER TABLE mytable 
    ALTER COLUMN mycolumn VARCHAR(10) 
    COLLATE SQL_Latin1_General_CP1_CI_AS