SQL Server – Prevent T-SQL Code from Breaking the Script

dynamic-sqlsql serversql-server-2000t-sql

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?

Best Answer

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
Related Question