Frank Kalis

DECLARE @string varchar(500)
SELECT
    @string = '%SomeString%'
 SELECT 
    SCP.usecounts, SCP.cacheobjtype, SCP.objtype, 
    [Statement].text, 
    Executionplan.query_plan 
FROM
    sys.dm_exec_cached_plans SCP
    OUTER APPLY sys.dm_exec_sql_text(plan_handle) [Statement]
    OUTER APPLY sys.dm_exec_query_plan(plan_handle) Executionplan
WHERE
    Statement.[text] LIKE @string AND
    SCP.cacheobjtype ='Compiled Plan' AND 
    SCP.objtype = 'proc'