Apr 192012
 

If you want to get the  Last Execution Date/Time of a Stored Procedure then here is solution.

Sys.dm_exec_procedure_stats it’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. This view has been introduced from SQL Server 2008.

The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

Script:

SELECT
PS.OBJECT_ID,
PS.DATABASE_ID,
OBJECT_NAME(OBJECT_ID, DATABASE_ID) 'PROC NAME',
PS.LAST_EXECUTION_TIME
FROM SYS.DM_EXEC_PROCEDURE_STATS AS PS

For more detail about this view one can refer http://msdn.microsoft.com/en-us/library/cc280701.aspx