SQL Server :: Clearing the plan cache for specific proc

It is very common to deal with situation where you have hundreds of procs in your database and you suspect  query plan is corrupted for one specific proc.

Following mentioned script will return plan handles and second script will help removing them one by one.

Please change database context accordingly.

**********************************************

1st, script to find all plan handles for one proc 

SELECT distinct cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%proc_name%';

2nd, script to clear cache just for this proc

SET NOCOUNT ON;
DECLARE @clear AS TABLE (id INT IDENTITY(1,1), p_handle VARBINARY(64))
DECLARE @minID INT = 0
DECLARE @p_handle VARBINARY(64) = NULL
DECLARE @SQL NVARCHAR(400) = '';

INSERT INTO @clear
SELECT distinct cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%proc_name%';

WHILE (SELECT COUNT(*) FROM @clear) > 0
BEGIN
SELECT @minID = MIN(id) FROM @clear;
SELECT @p_handle = p_handle FROM @clear WHERE id = @minID


SET @SQL = 'DBCC FREEPROCCACHE(0x' + CONVERT(VARCHAR(MAX), @p_handle, 2) +');'
EXECUTE sp_executesql @SQL
--PRINT @SQL

DELETE FROM @clear WHERE id = @minID

END;