SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations()

In my recent Office Hours, I received a question on how to determine the case sensitivity of the database. Let us learn about how we can Detecting Database Case Sensitive Property using fn_helpcollations().

The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.

FROM fn_helpcollations()

SQL SERVER - Detecting Database Case Sensitive Property using fn_helpcollations() helpcollations

The method shown above is the most recommended method and I suggest using the same.

When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.

WHERE 'SQL' = 'sql'

If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().

Another interesting suggestion was from Dave Dustin, who is a SQL Server MVP from New Zealand. He has provided the following script:

FROM sys.Databases
WHERE name='<databasename>'
AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')

Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.

It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Collation, SQL DMV, SQL Function, SQL Scripts, SQL Server
Previous Post
SQLAuthority News – SQL Wait Stats Book – Available as Kindle eBook – October Special
Next Post
SQL SERVER – Server Side Paging in SQL Server CE (Compact Edition)

Related Posts

5 Comments. Leave new

  • Fabricio Lima
    October 9, 2011 7:40 pm

    Hi Pinal Dave,

    This is an interesting information. I think we can use this querys below too:

    SELECT DATABASEPROPERTYEX(‘master’, ‘Collation’) SQLCollation

    select SERVERPROPERTY(N’Collation’)


    Fabricio Lima

  • gracias, esta informacion me agrada mucho ya que me ayuda mucho los aspectos netros

  • Suba Ganesh S
    October 11, 2011 3:29 pm

    How to make a Particular database as Case Sensitive….?

    • Goto the properties of the database. Under options choose the case sensitive collation that ends with CS_AS

  • Do we have option to change the property of case sensitive to case insenstive. Please let us know on this?


Leave a ReplyCancel reply

Exit mobile version