1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

DECLARE @ObjName varchar(255)
SET @ObjName = 'abc'
SELECT c.usecounts
	 , c.cacheobjtype
	 , c.objtype
	 , c.size_in_bytes
	 , t.text
	 , p.query_plan
	 , DB_NAME(p.dbid) AS DBNAME
	 , p.encrypted
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan (plan_handle) p
WHERE usecounts > 1 and t.text like '%'+@ObjName+'%'
  AND t.text not like '%sys.dm_exec_cached_plans%'
GO
--DBCC FREEPROCCACHE (plan_handle);
--ex) DBCC FREEPROCCACHE (0x05000C00F01C44754021DDDA040000000000000000000000);
GO