Check Plan Cache for Warnings

I was reading

https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/?utm_source=ssc&utm_medium=publink&utm_content=checkingplancache

And since I didn’t want to run his function for the ~150 databases I deal with on the production server, I wrote a variation to check the plan cache for warnings on all:


-- =============================================
-- Author: Derek Cate
-- Create date: 01/23/2015
-- Description: return the query plans in cache for all databases
-- based on work of Dennes Torres https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/?utm_source=ssc&utm_medium=publink&utm_content=checkingplancache
-- =============================================
alter FUNCTION [dbo].[planCachefromAllDatabase]
(
)
RETURNS TABLE
AS
RETURN
(
with xmlnamespaces
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select DB_NAME(qt.dbid) database_name,
qp.query_plan,qt.text,
statement_start_offset, statement_end_offset,
creation_time, last_execution_time,
execution_count, total_worker_time,
last_worker_time, min_worker_time,
max_worker_time, total_physical_reads,
last_physical_reads, min_physical_reads,
max_physical_reads, total_logical_writes,
last_logical_writes, min_logical_writes,
max_logical_writes, total_logical_reads,
last_logical_reads, min_logical_reads,
max_logical_reads, total_elapsed_time,
last_elapsed_time, min_elapsed_time,
max_elapsed_time
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
)
GO
-- =============================================
-- Author: Derek Cate
-- Create date: 01/23/2015
-- Description: Return the warnings in the query plans in cachefor all databases
-- based on work of Dennes Torres https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database/?utm_source=ssc&utm_medium=publink&utm_content=checkingplancache
-- =============================================
alter FUNCTION [dbo].[FindWarningsAllDatabases]
(
)
RETURNS TABLE
AS
RETURN
(
with xmlnamespaces
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
qry as
(select database_name,[text],
cast(nos.query('local-name(.)') as varchar) warning, total_Worker_time
from dbo.planCacheFromAllDatabase()
CROSS APPLY query_plan.nodes('//Warnings/*') (nos)
)
select database_name,[text],warning,count(*) qtd,max(total_worker_time) total_worker_time
from qry
group by database_name,[text],warning
)