SQL Server check case-sensitivity

Question

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

User · Answer

The best way to work with already created tables is that, Go to Sql Server Query Editor

Type: sp_help <tablename>

This will show table's structure , see the details for the desired field under COLLATE column.

then type in the query like :

SELECT myColumn FROM myTable  
WHERE myColumn COLLATE SQL_Latin1_General_CP1_CI_AS = 'Case'

It could be different character schema <SQL_Latin1_General_CP1_CI_AS>, so better to find out the exact schema that has been used against that column.

User · Answer

You re interested in the collation  You could build something based on this snippet   SELECT DATABASEPROPERTYEX  master    Collation        Update Based on your edit  mdash  If  test and  TEST can ever refer to two different variables  it s not SQL Server   If you see problems where the same variable is not equal to itself  check if that variable is NULL  because NULL   NULL returns  false

User · Answer

How can I check to see if a database in SQL Server is case-sensitive    You can use below query that returns your informed database is case sensitive or not or is in binary sort with null result      WITH collations AS       SELECT          name          CASE              WHEN description like   case-insensitive   THEN 0              WHEN description like   case-sensitive   THEN 1          END isCaseSensitive     FROM          sys fn helpcollations     SELECT   FROM collations WHERE name   CONVERT varchar  DATABASEPROPERTYEX  yourDatabaseName   collation       For more read this MSDN information

User · Answer

SQL Server is not case sensitive  SELECT   FROM SomeTable is the same as SeLeCT   frOM soMetaBLe

User · Answer

SQL server determines case sensitivity by COLLATION   COLLATION can be set at various levels    Server-level Database-level Column-level 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  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 name FROM customer ORDER BY name COLLATE Latin1 General CS AI    Collation Description  For getting description of each COLLATION value try this   SELECT   FROM fn helpcollations     And you should see something like this      You can always put a WHERE clause to filter and see description only for your COLLATION   You can find a list of collations here

User · Answer

Collation can be set at various levels    Server Database 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

User · Answer

If you installed SQL Server with the default collation options  you might find that the following queries return the same results    CREATE TABLE mytable         mycolumn VARCHAR 10      GO   SET NOCOUNT ON   INSERT mytable VALUES  Case    GO   SELECT mycolumn FROM mytable WHERE mycolumn  Case   SELECT mycolumn FROM mytable WHERE mycolumn  caSE   SELECT mycolumn FROM mytable WHERE mycolumn  case     You can alter your query by forcing collation at the column level    SELECT myColumn FROM myTable       WHERE myColumn COLLATE Latin1 General CS AS    caSE    SELECT myColumn FROM myTable       WHERE myColumn COLLATE Latin1 General CS AS    case    SELECT myColumn FROM myTable       WHERE myColumn COLLATE Latin1 General CS AS    Case    -- if myColumn has an index  you will likely benefit by adding  -- AND myColumn    case    SELECT DATABASEPROPERTYEX   lt database name gt     Collation      As changing this setting can impact applications and SQL queries  I would isolate this test first  From SQL Server 2000  you can easily run an ALTER TABLE statement to change the sort order of a specific column  forcing it to be case sensitive  First  execute the following query to determine what you need to change it back to    EXEC sp help  mytable     The second recordset should contain the following information  in a default scenario    Column Name Collation     mycolumn    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    If you are stuck with SQL Server 7 0  you can try this workaround  which might be a little more of a performance hit  you should only get a result for the FIRST match     SELECT mycolumn FROM mytable WHERE      mycolumn    case  AND      CAST mycolumn AS VARBINARY 10     CAST  Case  AS VARBINARY 10     SELECT mycolumn FROM mytable WHERE      mycolumn    case  AND      CAST mycolumn AS VARBINARY 10     CAST  caSE  AS VARBINARY 10     SELECT mycolumn FROM mytable WHERE      mycolumn    case  AND      CAST mycolumn AS VARBINARY 10     CAST  case  AS VARBINARY 10     -- if myColumn has an index  you will likely benefit by adding  -- AND myColumn    case

[sql] SQL Server check case-sensitivity?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to case-sensitive