Aqui está um desafio interessante que não consegui resolver... Há uma boa consulta T-SQL que eu, Jonathan Kehayias, criei há algum tempo para encontrar problemas de conversões implícitas nas consultas que eu amo. O problema é que essa consulta não funciona em bancos de dados com níveis de compatibilidade antigos (80 e anteriores). Suponho que isso ocorre porque as funções com valor de tabela (TVFs) foram introduzidas no SQL Server 2005.
O problema é que, se eu usar esse script para validar conversões implícitas em todos os meus bancos de dados assim:
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
... o script vai bombar porque 9 dos meus bancos de dados bizzilion estão no nível de compatibilidade 80! Você pode facilmente tentar isso criando um banco de dados em uma caixa com um nível de compatibilidade antigo e executando este script, você verá que ele falhará.
Como você pode ver, meu script está usando um TRY/CATCH, mas isso não ajudou porque o problema é na verdade um erro de compilação (e não um erro de tempo de execução).
Também tentei com um IF logo no início desse T-SQL dinâmico (como você pode ver), mas, novamente, os pontos de decisão não impedirão que o código seja compilado para esses bancos de dados e falhe.
Nada do que tentei até agora me ajuda a evitar esse erro e, como resultado, o trabalho que tenho relata como "falha" mesmo quando estou bem com esses bancos de dados específicos sendo "ignorados" se eu pudesse.
Algum de vocês tem uma idéia de como eu poderia implementar isso para que eu pudesse pesquisar em todos, exceto nos bancos de dados de nível de compatibilidade antigos?
Em vez de fazer a
sysdatabases.comptlevel
verificação dentro de seu SQL dinâmico, puxe osysdatabases.cmptlevel
check-out para o lote de nível superior e use-o para determinar em quais bancos de dados construir/executar consultas dinâmicas. Como alternativa, use ocmptlevel
para personalizar a consulta principal conforme necessário.Se você cria uma grande consulta SQL para atingir todos os bancos de dados desejados ou usa um cursor/loop para criar/executar uma consulta dinâmica para cada banco de dados desejado, é com você.
Um esboço rápido de pseudocódigo de uma solução de cursor/loop:
Você não precisa executar a consulta no contexto de cada banco de dados para ler seu catálogo. Você pode usar um nome de três partes como
mydb.sys.columns
. Isso também deve permitir que você inclua os 80 bancos de dados compatíveis.Então isso simplifica para: