Is there a way to figure out an executing query's plan handle by the query text?

Question

Is there a way to determine the plan handle for the query plan of an executing query by that query's text?

Perhaps in any of the DMVs?

(I'm trying to find the query stats on an ad-hoc query.)

Answer 1

Derived from https://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan

DECLARE @SearchForSql AS NVARCHAR(MAX) = 'SQL TEXT'

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE Text LIKE CONCAT('%' ,@SearchForSql,'%')
    AND Text NOT LIKE '%-- Self Reference Marker --%'

Note: The "NOT LIKE" is there to ensure that you don't find the statement you are running, which is inherently self referencing.