Working with Stored Procedure and Plan Handle

To view cache plan of AdHoc queries :

SELECT cplan.plan_handle, cplan.usecounts, cplan.objtype, qtext.text
,qplan.query_plan FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
where qtext.text like '%SELECT TOP (60)%'
ORDER BY cplan.usecounts DESC

To view cache plan of SP :

select cp.plan_handle from sys.dm_exec_cached_plans cp CROSS APPLY 
sys.dm_exec_sql_text(cp.plan_handle) st where 
OBJECT_NAME(st.objectid, st.dbid) = 'spName'

Sample Plan Hande Id :

0x05000500BD7ED44DF0EC7A0F7602000001000000000000000000000000000000000000000000000000000000

To delete plan handle :

DBCC FREEPROCCACHE (plan_handle_id)

To **drop **procedure :

drop procedure spName

To **view ** stored procedure's text :

sp_helptext spName

• Note : On query analyzer, Ctrl+T regular view, Ctrl+D grid view