How to get the names of the most expensive SPs in SQL Server 2005/2008

With the following query I get the list of top 10 most expensive queries with their SQL statements.

select top 10 * 
from sys.dm_exec_query_stats 
cross apply s开发者_StackOverflow社区ys.dm_exec_sql_text(sql_handle)
order by max_logical_reads desc

But how can I extract their object names ?

It's in the last column "text" for me on SQL Server 2005 SP3, like this

CREATE PROCEDURE dbo.myProc @parameter 1 int...

You could try OBJECT_NAME(objectid) but Metadata Visibility may prevent this (like it does me on my production boxes)

If you are dealing with persisent objects (i.e. stored procedures) then you can join on sysobjects like this:

select top 10 o.name from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) b 
inner join sys.sysobjects o on b.objectid = o.id
order by max_logical_reads desc

select top 10 OBJECT_NAME(objectid), * from sys.dm_exec_query_stats 
cross apply sys.dm_exec_sql_text(sql_handle)
order by max_logical_reads desc

automaticaly you can't. you'll have to parse them out yourself from the [Text] column