W poprzednim wpisie opisałem zjawisko CONVERT_IMPLICT. Teraz chcę przedstawić jeden ze skryptów, przy pomocy, którego można wyszukać miejsca występowania tego niepożądanego zjawiska.
Generalnie informację o występowaniu niejawnej konwersji typów danych możemy znaleźć w planie wykonania zapytań i skrypt właśnie przeszukuje te informacje
DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Batch,
+ 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)') AS The_ColumnReference,
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,
cp.usecounts,
pc.object_id,
s.name,
o.name,
pc.type_desc,
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 sys.dm_exec_procedure_stats AS pc on cp.plan_handle = pc.plan_handle
JOIN sys.objects o ON o.object_id = pc.object_id
JOIN sys.schemas s on o.schema_id = s.schema_id
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
ORDER BY cp.usecounts DESC
Poniżej jeszcze lekko zmieniony skrypt, który umożliwia „Normalne” wklejenie do Excela.
DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
Replace(
Replace(
replace(Left(stmt.value('(@StatementText)[1]', 'varchar(max)'),500),char(9),' ')
,char(10),'')
,char(13),'')
AS SQL_Batch,
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)') AS The_ColumnReference,
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,
cp.usecounts,
pc.object_id,
s.name,
o.name,
pc.type_desc
-- , 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 sys.dm_exec_procedure_stats AS pc on cp.plan_handle = pc.plan_handle
JOIN sys.objects o ON o.object_id = pc.object_id
JOIN sys.schemas s on o.schema_id = s.schema_id
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
ORDER BY cp.usecounts DESC
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
Left(stmt.value('(@StatementText)[1]', 'varchar(max)'),500) AS SQL_Batch,
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)') AS The_ColumnReference,
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,
cp.usecounts,
pc.object_id,
s.name,
o.name,
pc.type_desc
-- , 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 sys.dm_exec_procedure_stats AS pc on cp.plan_handle = pc.plan_handle
JOIN sys.objects o ON o.object_id = pc.object_id
JOIN sys.schemas s on o.schema_id = s.schema_id
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
ORDER BY cp.usecounts DESC
