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)'', ''varchar(max)''), t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)'', ''varchar(128)''), t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)'', ''varchar(128)''), t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)'', ''varchar(128)''), ic.DATA_TYPE AS ConvertFrom, ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, t.value(''(@DataType)'', ''varchar(128)'') AS ConvertTo, t.value(''(@Length)'', ''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)'', ''varchar(128)'') AND QUOTENAME(ic.TABLE_NAME) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)'', ''varchar(128)'') AND ic.COLUMN_NAME = t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)'', ''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?