SP; search Query plan cache for issues, like table scans, missing indexes or stats

SP; search Query plan cache for issues, like table scans, missing indexes or stats, or any other command

-- ********************************************************************************* 
-- Description 	:	search Query plan cache for issues, like table scans, missing 
			indexes or stats, or any other command
-- Parameters	:	@StringToSearchFor 
-- Author		:	Theo Ekelmans
-- Version		:	1.0
-- ********************************************************************************* 
--	Example Usage:
--	1. exec dbo.[spSearchPlanCache] '%<MissingIndexes>%'
--	2. exec dbo.[spSearchPlanCache] '%<ColumnsWithNoStatistics>%'
--	3. exec dbo.[spSearchPlanCache] '%<TableScan%'
--	4. exec dbo.[spSearchPlanCache] '%CREATE PROC%MessageWrite%'
-- ********************************************************************************* 

CREATE PROC [master].[dbo].[spSearchPlanCache] @StringToSearchFor VARCHAR(255)
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT	TOP 100
		st.text AS 'Sql'
		,cp.cacheobjtype
		,cp.objtype
		,DB_NAME(st.dbid)AS 'DatabaseName'
		,cp.usecounts AS 'Plan usage'
		,qp.query_plan
FROM	sys.dm_exec_cached_plans cp
			CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
			CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE	CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor
ORDER BY 
		cp.usecounts DESC