Change SQL server case sensitivity

Posted on 2009-02-09
Last Modified: 2012-05-06
Hi Experts
I have a big database (around 3000 tables) on SQL 2005 for which the collation is Latin1_General_BIN. It should be due to this, the table names and column names are case sensitive. We have developed a newer version of our application which talks to this database. The new application was developed on a test server/test db where the names are not case sensitive. Now wehn we try to   implement the new application, we realize that since the table names are case sensitive, the new application si giving error at many places. Is there an easy way to change the sql settings so that the original database/server is no more case sensitive?

Question by:bijualex
    LVL 2

    Expert Comment

    As far my knowledge, you can refer db objects in any capitalization, in other words its case-insensitive
    and that means

    you can say "SELECT COL1,COL2  FROM MYTABLE"
    "SELECT Col1,col2 from mYtABLE"

    and so on.

    What I feel is you better look at your application code,

    I think your application stored db column/table names in a variables and does some comparison, focus there

    Or am I wrong?
    LVL 37

    Expert Comment

    well, you would need to change the collation, which can be a big task to perform
    LVL 22

    Expert Comment


    If you have a case-sensitive collation references to db objects are case-sensitive.

    Heres an link how to change it:


    LVL 22

    Accepted Solution

    What you do is just:

    alter database tablename collate Latin1_General_CI_AS

    Author Comment

    I have tried to change the collation as specified in the above url. It gives me few erros saying that few constraints are dependent on database collation. I disabled these constraints, still I am getting the same error message. Any ideas?

    Author Comment

    Removed those constraints, changed the collation and added the constraints. The table names can now be references in any case :)

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now