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