主页 / dba / 问题 / 183851
Accepted
Martin Surasky
Asked: 2017-08-19 08:26:59 +0800 CST

防止不兼容的 T-SQL 代码破坏脚本

  • 772

这是一个有趣的挑战，我一直没能破解……我 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（如您所见），但同样，决策点不会阻止代码为这些数据库编译并失败。

到目前为止，我尝试过的任何方法都无法帮助我避免该错误，因此，即使我可以接受这些特定数据库被“跳过”（如果可以的话），我的工作也报告为“失败”。

你们中的任何人都知道我如何实现这个以便我可以搜索除了那些旧的兼容级别数据库之外的所有数据库吗？

sql-server t-sql

2 个回答

  1. Best Answer

    不要在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
    • 4

  2. 您不必在每个数据库的上下文中运行查询来读取其目录。您可以使用由三部分组成的名称，例如mydb.sys.columns. 这还应该允许您包括 80 个兼容数据库。

    所以这简化为：

    declare @sql    nvarchar(max)
set @sql =
N' 
    SET QUOTED_IDENTIFIER ON;
    DECLARE @dbname SYSNAME = ''[?]'';

    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


'

exec sp_msforeachdb @sql
    • 1

相关问题