How to determine if a connected database is case sensitive
I need to determine programmatically (C #) if the connected database (can be either Oracle 11g or Sql Server 2008) has been configured for case sensitivity, so I know when I compare two strings (one of them is retrieved from the database. one is entered by the user) whether to do a case comparison or not.
Is this possible, and if so, how to do it?
TIA
source to share
You can just use case
withselect
SELECT CASE WHEN 'A' = 'a' THEN 'Insensitive' ELSE 'Sensitive' END
The above is very important for the entire database session. To be more specific, you need to know more about the database settings for each DBMS.
The following checks for Oracle case sensitivity configuration:
SELECT CASE WHEN COUNT(*) = 2 THEN 'Insensitive' ELSE 'Sensitive' END
FROM NLS_SESSION_PARAMETERS
WHERE (PARAMETER = 'NLS_COMP' AND VALUE = 'LINGUISTIC')
OR (PARAMETER = 'NLS_SORT' AND VALUE = 'BINARY_CI')
The following checks are for specific SQL Server collation at different levels:
-- Server level
SELECT CASE WHEN SERVERPROPERTY('COLLATION') LIKE '%_CI_%' THEN 'Insensitive' ELSE 'Sensitive' END
-- Database level
SELECT CASE WHEN DATABASEPROPERTYEX('AdventureWorks', 'Collation') LIKE '%_CI_%' THEN 'Insensitive' ELSE 'Sensitive' END
-- Column level
SELECT CASE WHEN COLLATION_NAME LIKE '%_CI_%' THEN 'Insensitive' ELSE 'Sensitive' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
AND COLUMN_NAME = @column_name
It is not exhaustive for checking random collisions, but based on it SELECT name, description FROM ::fn_helpcollations()
, it looks pretty good.
Link for SQL Server mappings (as noted in OP comment)
- How to check: SQL Server check case-sensitivity?
source to share