防止不兼容的T-SQL程式碼破壞腳本

這是一個我無法破解的有趣挑戰… Jonathan Kehayias 前段時間創建了一個不錯的 T-SQL 查詢，用於查找我喜歡的查詢的隱式轉換問題。問題是，此查詢不適用於具有舊兼容級別（80 及之前）的數據庫。我認為這是因為 SQL Server 2005 中引入了表值函式 (TVF)。

問題是，如果我使用這個腳本來驗證我所有數據庫上的隱式轉換，如下所示：

declare @sql    nvarchar(4000)
set @sql =
'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''?'' AND compatibility_level >= 90)
BEGIN   
   USE ['+'?'+'] ;
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET QUOTED_IDENTIFIER ON
   DECLARE @dbname SYSNAME 
   SET @dbname = QUOTENAME(DB_NAME())

   BEGIN TRY
       RAISERROR(''?'', 0, 42) WITH NOWAIT;
       WITH XMLNAMESPACES 
           (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'') 
       INSERT INTO DMTAdmin.dbo.BestPractices_ImplicitConversions
       SELECT 
           GETDATE(),
           @dbname,
           stmt.value(''(@StatementText)[1]'', ''varchar(max)''), 
           t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)''), 
           t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)''), 
           t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)''), 
           ic.DATA_TYPE AS ConvertFrom, 
           ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
           t.value(''(@DataType)[1]'', ''varchar(128)'') AS ConvertTo, 
           t.value(''(@Length)[1]'', ''int'') AS ConvertToLength, 
           query_plan 
       FROM sys.dm_exec_cached_plans AS cp 
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
       CROSS APPLY query_plan.nodes(''/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple'') AS batch(stmt) 
       CROSS APPLY stmt.nodes(''.//Convert[@Implicit="1"]'') AS n(t) 
       JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
           ON QUOTENAME(ic.TABLE_SCHEMA) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)'') 
           AND QUOTENAME(ic.TABLE_NAME) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)'') 
           AND ic.COLUMN_NAME = t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)'') 
       WHERE t.exist(''ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]'') = 1
   END TRY
   BEGIN CATCH
   END CATCH;
END
'

exec sp_msforeachdb @sql

…腳本會爆炸，因為我的 bizzilion 數據庫中有 9 個處於兼容級別 80！您可以通過在具有舊兼容級別的框中創建一個數據庫並執行此腳本來輕鬆嘗試此操作，您將看到它會失敗。

如您所見，我的腳本正在使用 TRY/CATCH，但這並沒有幫助，因為問題實際上是編譯錯誤（而不是執行時錯誤）。

我也嘗試在動態 T-SQL 的開頭使用 IF（如您所見），但同樣，決策點不會阻止為這些數據庫編譯程式碼並失敗。

到目前為止，我所嘗試的任何方法都無法幫助我避免該錯誤，因此，即使我可以接受這些特定數據庫被“跳過”，如果可以的話，我的工作也會報告為“失敗”。

你們中是否有人知道我該如何實現這一點，以便我可以搜尋除那些舊的兼容級別數據庫之外的所有數據庫？

與其在sysdatabases.comptlevel動態 SQL 中進行檢查，不如將sysdatabases.cmptlevel檢查拉到頂級批處理並使用它來確定要針對哪些數據庫建構/執行動態查詢。或者，cmptlevel根據需要使用 自定義主查詢。

您是建構一個大型 SQL 查詢來訪問所有所需的數據庫，還是使用游標/循環為每個所需的數據庫建構/執行動態查詢，這取決於您。

游標/循環解決方案的快速虛擬碼大綱：

declare @dbname varchar(30), @cmptlevel int, @sql varchar(max)
declare dbcur cursor for select name, cmptlevel from sysdatabases
open dbcur
fetch dbcur into @dbname, @cmptlevel
while @@sqlstatus = 0
begin
   select @sql='... common query code ... add '@dbname..' prefix to tables as needed ... '+
               case when @cmptlevel <  90 then '... cmptlevel< 90 specific code ... add '@dbname..' prefix to tables as needed ...'
                    when @cmptlevel >= 90 then '... cmptlevel>=90 specific code ... add '@dbname..' prefix to tables as needed ...'
               end + 
               '... common query code ... add '@dbname..' prefix to tables as needed ... '
   exec sp_excecutesql @sql
   fetch dbcur into @dbname, @cmptlevel
end
close dbcur

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