2

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?

4

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
  • This is what I would do. Check for compatibility in a separate procedure before calling the compatibility-mode specific code. You could call a separate procedure entirely to keep the versions of code separate. – Michael Capobianco Aug 18 '17 at 19:34
  • @MichaelCapobianco: I've incorporated your idea aobut the different versions of code into my answer; [NOTE: I'm not familiar with the OP's query so don't know if customization would be needed] – markp Aug 18 '17 at 21:01
  • Thanks for your suggestion markp! I ended up implementing something very similar to this. – Martin Surasky Aug 18 '17 at 21:01
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.