Finding Implicit Column Conversions in the Plan Cache made usable

a_find_implicit_conversions.sql

/*

    Based on http://www.sqlskills.com/blogs/jonathan/finding-implicit-column-conversions-in-the-plan-cache/

    Results made (mostly) usable by deduping on statements and ordering output by tables and columns

    Uses sys.columns instead of INFORMATION_SCHEMA.COLUMNS for performance (nothing gained here)

*/

IF OBJECT_ID('tempdb..#TmpImplicitConv') IS NOT NULL DROP TABLE #TmpImplicitConv

CREATE TABLE #TmpImplicitConv (
              StatementText         NVARCHAR(MAX)
            , StatementChecksum AS (CHECKSUM(StatementText))
            , TableSchema           NVARCHAR(128)
            , TableName             NVARCHAR(128)
            , ColumnName            NVARCHAR(128)
            , ConvertFrom           NVARCHAR(128)
            , ConvertTo             NVARCHAR(128)
            , ConvertFromLength     INT
            , ConvertToLength       INT
            , ConvertFromPrecision  INT
            , ConvertToPrecision    INT
            , ConvertFromScale      INT
            , ConvertToScale        INT
            , QueryPlan             XML
            )
CREATE UNIQUE CLUSTERED INDEX #IX_TmpImplicitConv_Dedupe 
ON          #TmpImplicitConv(StatementChecksum, TableSchema, TableName, ColumnName)
WITH        (IGNORE_DUP_KEY = ON)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE     @dbname SYSNAME 
SET         @dbname = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
INSERT      #TmpImplicitConv
SELECT      stmt.value('(@StatementText)[1]', 'NVARCHAR(MAX)')      AS StatementText
            , t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'NVARCHAR(128)')    AS TableSchema
            , t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'NVARCHAR(128)')     AS TableName
            , t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'NVARCHAR(128)')    AS ColumnName
            , TYPE_NAME(ic.system_type_id)                          AS ConvertFrom
            , t.value('(@DataType)[1]', 'NVARCHAR(128)')            AS ConvertTo
            , CASE WHEN ic.is_ansi_padded = 1 OR ic.collation_name IS NOT NULL THEN
                ic.max_length END                                   AS ConvertFromLength
            , t.value('(@Length)[1]', 'INT')                        AS ConvertToLength
            , CASE WHEN ic.scale <> 0 THEN NULLIF(ic.precision, 0) END AS ConvertFromPrecision
            , t.value('(@Precision)[1]', 'INT')                     AS ConvertToPrecision
            , NULLIF(ic.scale, 0)                                   AS ConvertFromScale
            , t.value('(@Scale)[1]', 'INT')                         AS ConvertToScale
            , query_plan                                            AS QueryPlan
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        sys.columns AS ic 
ON          QUOTENAME(OBJECT_SCHEMA_NAME(ic.object_id, DB_ID())) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'NVARCHAR(128)') 
            AND QUOTENAME(OBJECT_NAME(ic.object_id)) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'NVARCHAR(128)') 
            AND ic.name = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'NVARCHAR(128)') 
WHERE       t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

SELECT      *
FROM        #TmpImplicitConv
ORDER BY    TableSchema
            , TableName
            , ColumnName