roy ashbrook

select
    case when dbid = 32767
        then 'resource'
        else db_name(dbid)
    end [db_name]
    , object_schema_name(objectid,dbid) [schema_name]
    , object_name(objectid,dbid) [object_name]
    , sum(usecounts) [executions]
    , max(max_execution_time) [last_execution_time]
    , sum(total_logical_reads) [total_logical_reads]
    , sum(total_logical_reads) / sum(usecounts) * 1.0 [avg_logical_reads]
    , sum(total_elapsed_time) / 1000 [total_elapsed_time_ms]
    , ( sum(total_elapsed_time) / sum(usecounts) * 1.0 ) / 1000 [avg_elapsed_time_ms]
from
    sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_sql_text(cp.plan_handle) qt
    join (
        select
            plan_handle
            , max(last_execution_time) [max_execution_time]
            , sum(total_elapsed_time) [total_elapsed_time]
            , sum(total_logical_reads) [total_logical_reads]
        from
            sys.dm_exec_query_stats
        group by
            plan_handle
        ) r on r.plan_handle = cp.plan_handle
where
    objtype = 'Proc'
    and qt.text LIKE '%create%proc%'
    --and db_name(dbid) = 'some_database'
    --and object_name(objectid,dbid) = 'some_stored_procedure'
group by
    dbid, objectid
order by
    db_name(dbid), object_name(objectid,dbid)

thanks to this article for the inspiration.