How to clear ad hoc queries from plan cache?

So you just want to clear the Ad-hoc query plans and still dont want to clear the whole procedure cache. What you are asking is there in Clearing Your Ad-hoc SQL Plans While Keeping Your SP Plans Intact

The blog asks you to run

DBCC FREESYSTEMCACHE('SQL Plans')

As per the blog

The procedure cache actually consists of 4 distinct cache stores that hold different types of plans. Those cache stores are:

• CACHESTORE_OBJCP - these are "Object Plans" - stored procedures, functions and triggers. Generally, the good stuff.
• CACHESTORE_SQLCP - these are "SQL Plans" - ad-hoc SQL statements (including parameterized ones) and prepared statements. This is the stuff that we are after.
• CACHESTORE_PHDR - so-called "Bound Trees" for views, constraints and defaults. Irrelevant for the problem discussed.
• CACHESTORE_XPROC - not really execution plans but rather pointers to the entry points of your extended SPs.

So you can see selectively clearing the SQLCP would remove prepared and ad-hoc plans. I tested this on my system.

Ran query

select objtype, 
count(*) as number_of_plans, 
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
--where objtype='adhoc'
group by objtype

And the output was

You can see the the picture has 1264 ad-hoc plans and 69 prepared statements.

Now I selectively clear the SQLCP using DBCC FREESYSTEMCACHE('SQL Plans') and rerun the query again which gave me below output

Now you can see the ad-hoc and prepared plans are 2 and 6 respectively. While other are NOT affected.

DECLARE @plan_handle varbinary(64)

DECLARE db_cursor CURSOR FOR 
SELECT plan_handle
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' -- and usecounts = 1 -- optional: just delete the ones that are used only once

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @plan_handle  

WHILE @@FETCH_STATUS = 0  
BEGIN  
    DBCC FREEPROCCACHE (@plan_handle);  
    FETCH NEXT FROM db_cursor INTO @plan_handle 
END 

CLOSE db_cursor  
DEALLOCATE db_cursor