List of cached query plans

dm_exec_cached_plans returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

SELECT
      CP.[objtype] -- ad hoc query, stored procedure, prepared statement, etc.
    , CP.[cacheobjtype]
    , CP.[size_in_bytes]
    , CP.[refcounts]
    , CP.[usecounts]
    , ST.[text]
    , QP.query_plan
FROM
    sys.dm_exec_cached_plans as CP
    CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) as ST
    CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
    1 = 1
    AND ST.[text] LIKE '%<string to find>%'
    --AND CP.[objtype] NOT IN (N'Adhoc', N'Prepared')
ORDER BY
    CP.[objtype]       

Is this useful?