Quite often , this question appears on forums or on various jobs.”Is there any way that I can tell when a stored procedure was last executed or who executed it? Is there any execution history that I can look at?”

The following query will assist in telling you what time a stored procedure was last executed – assuming the query hasn’t been flushed from the cache.

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);

Ref:Jack Vamvas