SQL 2005 CROSS APPLY

Firstly for using cross apply,
You need to check the compability of a database simply by running the below sql command.

sp_dbcmptlevel 'DatabaseName'

If the compatibilty is less than 90, the CROSS APPLY command will not work.
Change it to 90 using:

EXEC sp_dbcmptlevel DatabaseName, 90;
GO


To find all the most recently executed procs using cross apply use the sql below:

This tells the last time the procs were executed in recent month or so depending upon the capacity of cache. It does not give the last executed time information on all procedures in tha database. If the proc is executed only once a month or less frequently, then it might have been pushed out of the cache by the time we actually check the stats. If we want something better, we will have to login information in a table everytime the proc fires. (I am just an amateur on this so incase, there is a better solution, please feel free to post here and I did be glad to learn more)

SELECT a.execution_count ,
OBJECT_NAME(objectid) Name,
query_text = SUBSTRING(
b.text,
a.statement_start_offset/2,
( CASE WHEN a.statement_end_offset = -1
THEN len(convert(nvarchar(max), b.text)) * 2
ELSE a.statement_end_offset
END - a.statement_start_offset)/2
) ,
b.dbid ,
dbname = db_name(b.dbid) ,
b.objectid ,
a.creation_time,
a.last_execution_time,
a.*
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b
WHERE OBJECT_NAME(objectid) = ''
ORDER BY a.last_execution_time DESC