SQL server stores the execution plan of all the statements in its cache before it actually executes. In order to find the last execution we just need to query the sys.dm_exec_query_stats DMV.
SELECT st.text as SQL,qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%YourSpName%'
Note: contents of the DMVs will clear while restarting the service, so the contents wont always be accurate.
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.text like '%YourSpName%'
Note: contents of the DMVs will clear while restarting the service, so the contents wont always be accurate.
