SQL Server 检查区分大小写?

2021-12-02 00:00:00 sql tsql sql-server case-sensitive

如何检查 SQL Server 中的数据库是否区分大小写?我之前一直在运行查询:

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.

编辑 - 更多信息:现有产品具有许多预先编写的存储过程.在存储过程中 @test != @TEST 取决于服务器本身的敏感性.所以我正在寻找的是检查服务器敏感性的最佳方法.

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.

推荐答案

Collat​​ion 可以在不同级别设置:

Collation can be set at various levels:

  1. 服务器
  2. 数据库
  3. 列

因此，您可以在不区分大小写的数据库中设置区分大小写的列.我还没有遇到过可以针对单列数据的大小写敏感度制定业务案例的情况，但我想可能会.

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.

检查服务器整理

SELECT SERVERPROPERTY('COLLATION')

检查数据库整理

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

检查列整理

select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name

相关文章