SELECT name, collation_name
FROM sys.databases

SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its “COLLATION” property and look for “CI” or “CS” in the result.

  • CI = Case Insensitive
  • CS = Case Sensitive
  1. To check if a server is case sensitive, run this query: SELECT SERVERPROPERTY('COLLATION') A common result is:SQL_Latin1_General_CP1_CI_AS The CI indicates that the server is case insensitive.
  2. To check if a specific SQL Server database is case sensitive, run this query:SELECT collation_name FROM sys.databases WHERE name = 'your_database_name'Again, this will output something like:SQL_Latin1_General_CP1_CI_AS
  3. To check all databases on the server, just leave out the WHERE clause and include name in the SELECT list:SELECT name, collation_name FROM sys.databases
  4. To check the collation for all columns in a SQL Server database table, run this query:SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'your_table_name' AND CHARACTER_SET_NAME IS NOT NULL

Another command for checking the case-sensitivity and other properties of the database server is:

EXECUTE sp_helpsort

This will return something like:

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Finally, to see all collations supported by your SQL Server installation, run this:

SELECT name, description FROM sys.fn_helpcollations()


Last modified: February 1, 2023



Write a Reply or Comment