# Working with Stored Procedure and Plan Handle

To view **cache plan** of AdHoc queries :

```csharp
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 :

```csharp
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 :

```csharp
0x05000500BD7ED44DF0EC7A0F7602000001000000000000000000000000000000000000000000000000000000
```

To delete **plan handle** :

```csharp
DBCC FREEPROCCACHE (plan_handle_id)
```

To \*\*drop \*\*procedure :

```csharp
drop procedure spName
```

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

```csharp
sp_helptext spName
```

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