How to remove a specific query plan from cache.

Sometimes our query cache would use the same table structure even if the table structure has been changed say we added or removed more columns , but the query cache still would follow old schema and we receive error that schema doesnt match.

Example:Error Line: 262; Error Severity: 16; Error State: 4;
Error Message: Could not complete cursor operation because the table schema changed after the cursor was declared.

In such cases below query becomes handy to clear the query cache

-- Use the below query to get the handle of the query which is being run
SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT * FROM TableA%'
GO

-- Use the SQL Handle to remove the specific plan from the cache
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000)
GO