Get Stored Procedure Last Run Time
Hello!
Quick script to get the last time a stored procedure was executed in the database. The reason for the seemingly over-engineered script is that different query plans can be generated, meaning that stored procedures can appear more than once in the list.
SELECT OBJECT_NAME(qt.objectid) AS [StoredProcedure]
,qs.execution_count AS [Execution Count]
,qs.creation_time AS [Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
INNER JOIN (
SELECT OBJECT_NAME(qt.objectid) AS [sproc]
,qs.execution_count AS [count]
,MAX(qs.creation_time) AS [latest]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = DB_ID()
GROUP BY OBJECT_NAME(qt.objectid)
,qs.execution_count
) AS subquery ON OBJECT_NAME(qt.objectid) = subquery.sproc
AND qs.creation_time = subquery.latest
WHERE qt.[dbid] = DB_ID()
AND subquery.sproc LIKE '%%'
GROUP BY OBJECT_NAME(qt.objectid)
,qs.execution_count
,qs.creation_time
OPTION (RECOMPILE);
Happy scripting!