Prevent T-SQL code that is not compatible to break the script

Question

Here is an interesting challenge that I have not been able to crack... There is one nice T-SQL query that I Jonathan Kehayias created some time ago to find Implicit Conversions issues on the queries that I love. The thing is, this query doesn't work on databases with old compatibility levels (80 and before). I assume this is because Table Valued Functions (TVFs) where introduced in SQL Server 2005.

The problem is, if I use this script to validate implicit conversions on all my databases like this:

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

... the script will bomb because 9 out of my bizzilion databases are in compat level 80! You can easily try this by creating one database in a box with an old compat level and run this script, you will see that it will fail.

As you can see my script is using a TRY/CATCH, but that has not helped because the problem is actually a compilation error (and not a run time error).

Also I tried with an IF right at the beginning of that dynamic T-SQL (as you can see) but again, decision points will not prevent the code from being compailed for those databases and failing.

Nothing that I have tried so far helps me to avoid that error and as a result, the job I have reports as "failure" even when I'm OK with these specific databases being "skipped" if I could.

Do any of you guys have an idea of how could I implement this so that I could search on all but those old compat level databases?

Answer 1

Instead of doing the sysdatabases.comptlevel check inside of your dynamic SQL, pull the sysdatabases.cmptlevel check out to the top-level batch and use it to determine which databases to build/run dynamic queries against. Alternatively, use the cmptlevel to customize the main query as needed.

Whether you build one big SQL query to hit all desired databases, or use a cursor/loop to build/run a dynamic query for each desired database, is up to you.

A quick, pseudo-code outline of a cursor/loop solution:

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

Answer 2

You don't have to run the query in the context of each database to read its catalog. You can use a three-part name like mydb.sys.columns. This should also allow you to include the 80 compat databases.

So this simplifies to:

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