Sometimes you need to know when a specific stored procedure was last executed. The following query will assist in telling you the last execution time of your stored procedures. We are assuming the query hasn’t been flushed from the cache. The will only work with SQL Server 2008 and higher.
SELECT object_name(m.object_id), MAX(qs.last_execution_time) FROM sys.sql_modules m LEFT JOIN (sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st) ON m.object_id = st.objectid AND st.dbid = db_id() GROUP BY object_name(m.object_id);
Another way is to manually script a logging event in the stored procedures you wish to track and insert the information in a custom logging table.