Sql-Server

SSMS - 如何在對象資源管理器中進行不區分大小寫的搜尋

  • January 30, 2019

**前言：**我不是 指查詢。

SSMS 中有許多地方允許過濾，例如對象資源管理器和探查器，但這些都將過濾器視為區分大小寫，否則沒有可見選項，因此如果您搜尋contains 'ASDF'“ASDF_MyEntity”之類的值，但“asdf_MyEntity” “被省略。

在此處輸入圖像描述

例如，我們在大型伺服器上有很多SQL 代理作業，我嘗試使用對象資源管理器按項目名稱過濾它們，我們總是在作業名稱前加上前綴。但是，這些顯示為大寫和小寫變體。

另一個案例是在數千個條目中搜尋與模組相關的儲存過程。如果一個名稱不一致（例如 PascalCase 與 camelCase），過濾後的搜尋將忽略它。這似乎是調試幻象的不必要的危險。

此外，對於每種類型的實體（例如表名、儲存過程、作業名等），對象資源管理器中的排序將大寫變體放在小寫之前（所以大寫Z在小寫之前a），所以我要麼必須滾動很多，或檢查兩個不同的過濾器（如果不是全部大寫或全部小寫字母，則檢查更多 - 準確地說是 2 len(name)次）。

我意識到我可以手動查詢jobs、tables和其他實體，但鑑於 SSMS 存在，這樣做是荒謬的，因為它的名稱是“SQL Server Management Studio”。

我可以做些什麼來讓 SSMS（和 SQL Server Profiler）像其他所有 Windows 應用程序一樣忽略大小寫？也許我可以在本地更改排序規則設置？

另外，為什麼微軟在實施 SSMS 時會做出這個決定？我發現它只是有害的。

此外，SSMS 的載入初始螢幕顯示“在 Visual Studio 上建構”，它忽略了解決方案資源管理器中的大小寫。

PS 使用 SSMS 2014（版本 12.0.5214.6）。我無法進行任何伺服器端更改，並且我的本地開發環境應與目標伺服器環境匹配以進行測試。

在技​​術上可以進行這種不區分大小寫的過濾，但這只是由於 SSMS 中的一個錯誤。我沒有 SSMS 2014（版本 12.x），但我確實讓它在 SSMS 17.9.1 和 18.0 Preview 6 中工作，連接到預設排序規則為Hebrew_100_BIN2.

嘗試一（否）

看到查詢使用LIKE，我想我會嘗試使用單字元範圍萬用字元（即[和]）。然而，由於某些字元被轉義，傳遞一個模式[Tt][Ee][Ss][Tt]不起作用。似乎[,%和_都被包裹起來[並將]它們變成文字（即不再具有特殊含義）。

嘗試 Dos（是）

看到與LIKE運算符一起使用的值（即輸入到 UI 中的值）被連接到查詢中，而不是作為變數傳入（即，tbl.name like N''%Test%''而tbl.name like N''%'' + @_msparam_XX + N''%''不是用使用者輸入來做這個？嗯，我想知道……“所以我傳入 a '，它被轉義為''. 通常這正是單引號應該發生的情況。''但是，這是在動態 SQL 中使用的，因此它的嵌套比正確轉義的位置更深一層。字元串中的字元串需要將單引號轉義為''''或雙引號，因為''僅結束字元串中的字元串。

例如，在過濾“表格”時，生成的程式碼（重要的一行）是：

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Test%'')

如果我通過了“測試”，生成的程式碼將是：

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Te''st%'')

是的，它確實得到一個“未閉合的引號”錯誤。

我嘗試添加一個COLLATE子句，但所有排序規則都有下劃線，所以Latin1_General_100_CI_AS變成Latin1[_]General[_]100[_]CI[_]AS. 哦！

我們不能傳入[或不傳入那些被and%包裹的東西，但是，我們可以將東西連接到字元串上，例如函式的輸出。而且，我們可以通過將它們作為十六進制/值傳遞來繞過和字元的轉義，然後將它們返回一次。[``]``N'%``[``%``VARBINARY``CONVERT``NVARCHAR

因此，在過濾儲存過程時，我們從正常生成的程式碼開始：

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1

為了完成這項工作，我們需要傳入一個具有以下結構的字元串（代替“Test”）：

' + CONVERT(NVARCHAR(MAX), 0x.....) AND 'x' <> '

我們可以--在末尾使用而不是AND 'x' <> '，但該AND構造確保如果在該部分之後有查詢的其他部分並且在同一行上，則它會繼續按預期執行。

要生成我們的“轉義”過濾器，我們可以使用以下內容：

SELECT CONVERT(VARBINARY(MAX), N'[Tt][Ee][Ss][Tt]%'); -- must end with "%"
-- 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500

將該十六進制值粘貼到我們的 SQL 注入解決方法中，我們得到：

' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '

如果我們將過濾器設置為上面的值，生成的程式碼將是：

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%'' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND ''x'' <> ''%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'1'

並將生成的程式碼傳遞到sp_executesql. 執行的語句在 Profiler 中顯示為：

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N'%' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '%')

這種技術之所以有效，是因為0xYYYY....它不是解析器正在尋找的東西。它只是尋找 、 和 的字面值，[我們並沒有將它們傳遞進去。我們只是傳遞了一些東西，一旦動態 SQL 被執行，它們就會被翻譯成這些值。_``%

但它不需要VARBINARY是傳入的字元串。任何傳回字元串的函式都可以工作。您只需要將結尾N'%與某事連接起來，然後是之前出現的內容%')，因此：' + {something_ending_with_%'} AND 'x' <> '. 我本可以用來+ NCHAR(91) + N'Tt' + NCHAR(93) +表示[Tt]，但這似乎比連續VARBINARY文字大得多。但是，如果您只想要一個%，那麼做+ NCHAR(37) + N'就可以了。請記住使用NCHAR()和前綴文字，N因為這是所有NVARCHAR數據。

嘗試三（非常肯定）

因此，您不希望[Tt][Ee][Ss][Tt]每次都創建字元串並將其轉換為VARBINARY. 我明白了。哦，您無權在 SQL Server 中添加/更改對象。行。但是您確實可以訪問tempdb，對嗎？如果你可以在 中創建一個函式tempdb，那麼這會更容易。只需執行以下操作：

USE [tempdb];
IF (OBJECT_ID(N'dbo.ObjectFilter') IS NOT NULL)
BEGIN
 DROP FUNCTION dbo.ObjectFilter;
END;

GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
 IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
 BEGIN
   RETURN 1;
 END;

 RETURN 0;  
END;
GO

然後將過濾器設置為：

' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '

生成的程式碼將是：

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N'%' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '%')

您將不得不調整列別名，因為表使用tbl.name而不是sp.name. 但是現在您可以傳入任何字元串，它將使用不區分大小寫的排序規則進行比較！

而且，此功能將一直存在，直到實例重新啟動。因此，您只需要每隔一段時間執行一次上述 T-SQL。

{ 丟下麥克風 … 走下舞台 } 😎

補充筆記：

  1. 請記住，這種技術之所以有效，是因為 SSMS 中存在錯誤。如果該錯誤得到修復，那麼這種解決方法可能會停止工作。請參閱底部的“更新”部分以獲取指向我送出的關於此問題的錯誤報告的連結，以及我送出的增強建議，要求每個可過濾屬性的“不區分大小寫”複選框。
  2. 從蘭迪的回答：

除非您找到查詢的儲存位置並更改它，否則…

尋找查詢毫無意義。它們來自構成 SSMS 的 DLL。我認為C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.Sdk.Sfc.dll可能是其中之一，但不確定。 3. 從鮑勃對這個問題的評論：

SSMS 不強制區分大小寫，除非伺服器排序規則區分大小寫。

這要看情況。排序規則有兩個級別——實例級別和數據庫級別——重要的一個取決於某人試圖過濾的元數據。實例級排序影響過濾實例級元數據（例如登錄、連結伺服器等）和系統數據庫中的元數據。數據庫級排序規則影響過濾使用者數據庫元數據（例如使用者、表等）。在包含/部分包含的數據庫中，過濾數據庫級元數據將始終使用排序規則：Latin1_General_100_CI_AS_WS_KS_SC.

這種級別的分離解釋了 Randi 的回答中提到的以下發現：

在 CI 伺服器上的 CS DB 上似乎也不可能：

  1. 來自 Elaskanator 對這個問題的評論：

目前它設置為區分大小寫的二進制排序規則 (Latin1_General_BIN)。

僅供參考：不幸的是，這是一個非常普遍的誤解，但是不，二進制排序規則不區分大小寫

更新

我已向 Microsoft 送出以下回饋項：

  1. BUG：SSMS：對象資源管理器過濾允許 SQL 注入 (oops)
  2. 建議：SSMS：允許在對象資源管理器過濾器中強制不區分大小寫匹配

使用分析器擷取應用Contains過濾器時執行的查詢，我找到了查詢：

   exec sp_executesql N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
       serverproperty(N''Servername'')
      AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
case 
   when sp.is_ms_shipped = 1 then 1
   when (
       select 
           major_id 
       from 
           sys.extended_properties 
       where 
           major_id = sp.object_id and 
           minor_id = 0 and 
           class = 1 and 
           name = N''microsoft_database_tools_support'') 
       is not null then 1
   else 0
end          
            AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'0'

其中包含部分：

and sp.name like N''%Test%''

並給我結果：

Name    Schema  Urn CreateDate  Owner   IsEncrypted ImplementationType  IsNativelyCompiled
TestCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:28.220 dbo 0   1   0

正如@BobKlimes 所指出的，這意味著它在 CS 伺服器上是不可能的。

在 CI 伺服器上的 CS DB 上似乎也不可能：

在此處輸入圖像描述

因此，我們遇到了與您在添加的連結中提到的相同的問題。

除非您找到查詢的儲存位置並更改它，否則我認為沒有辦法解決它。

嘗試使用計劃指南添加排序規則提示

下面的查詢確實給了我想要的東西，因為我添加了 COLLATE 提示，所以我嘗試使用整理查詢的查詢計劃 XML 在原始查詢上創建計劃指南。

為我提供所需過濾的查詢

exec sp_executesql N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
       serverproperty(N''Servername'')
      AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
case 
   when sp.is_ms_shipped = 1 then 1
   when (
       select 
           major_id 
       from 
           sys.extended_properties 
       where 
           major_id = sp.object_id and 
           minor_id = 0 and 
           class = 1 and 
           name = N''microsoft_database_tools_support'') 
       is not null then 1
   else 0
end          
            AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%''COLLATE Latin1_General_CI_AS)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'0'

結果：

Name    Schema  Urn CreateDate  Owner   IsEncrypted ImplementationType  IsNativelyCompiled
testCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:23.830 dbo 0   1   0
TestCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:28.220 dbo 0   1   0

創建計劃指南（XML 由於太大而未顯示）遵循此答案

EXEC sp_create_plan_guide 
   @name = 'Filtering', 
   @stmt = N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
       serverproperty(N''Servername'')
      AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
case 
   when sp.is_ms_shipped = 1 then 1
   when (
       select 
           major_id 
       from 
           sys.extended_properties 
       where 
           major_id = sp.object_id and 
           minor_id = 0 and 
           class = 1 and 
           name = N''microsoft_database_tools_support'') 
       is not null then 1
   else 0
end          
            AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',
   @type = 'SQL',
   @params = N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',
   @hints = @planXML
GO

雖然我可以創建計劃指南，但似乎並不堅持原始查詢。

Commands completed successfully.

不說

Name    Schema  Urn CreateDate  Owner   IsEncrypted ImplementationType  IsNativelyCompiled
TestCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:28.220 dbo 0   1   0

發現錯誤

SELECT *
FROM sys.plan_guides 
   CROSS APPLY sys.fn_validate_plan_guide (plan_guide_id)
WHERE name = 'Filtering2'

在此處輸入圖像描述

嘗試使用不同的方法將 XML 計劃添加到計劃指南時發生相同的錯誤

DECLARE @xml_showplan nvarchar(max);  
SET @xml_showplan = (SELECT query_plan  
   FROM sys.dm_exec_query_stats AS qs   
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
   CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
   WHERE st.text LIKE N'%uses_native_compilation%' and  st.text like '%COLLATE%'

在此處輸入圖像描述

我的猜測是它的縮寫：

NO_PLAN：查詢處理器無法生成查詢計劃，因為無法驗證強制計劃對查詢是否合法

來源

我可能正在做一些計劃指南無法做到的事情。

引用自：https://dba.stackexchange.com/questions/228269

相關問答

Sql-Server

在 ssms 中模擬區分大小寫的排序規則

  • July 15, 2022
檢視問答
Sql-Server

使用不同的區分大小寫排序規則時的 SQL Server PATINDEX 問題/錯誤

  • June 28, 2022
檢視問答
Sql-Server

為什麼在具有越南語_CI_AI 排序規則的 SQL Server 上比較 ’tr’ 和 ’tR’ 失敗？

  • August 26, 2021
檢視問答
Sql-Server

是否可以為數據與單個數據庫中的實體指定不同的排序規則？

  • September 22, 2020
檢視問答
Sql-Server

打開大型 SQL Server 文件時出現“系統找不到指定的文件”錯誤

  • September 1, 2020
檢視問答
Sql-Server

擴展事件歸類解決錯誤

  • December 13, 2019
檢視問答