store procedure - last execution

Hi experts

it is possible to know when he was executed (date and time) for the last time a stored procedure
enrique_aeoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
check this.

http://sqlfool.com/2009/08/find-recently-executed-stored-procedures/

Select DB_Name(dest.[dbid]) As 'databaseName'
    , Object_Name(dest.objectid, dest.[dbid]) As 'procName'
    , Max(deqs.last_execution_time) As 'last_execution'
From sys.dm_exec_query_stats As deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) As dest
Where dest.[text] Like '%yourTableName%' -- replace
    And dest.[dbid] Is Not Null  -- exclude ad-hocs
Group By db_name(dest.[dbid])
    , Object_Name(dest.objectid, dest.[dbid])
Order By databaseName
    , procName
Option (MaxDop 1);

Open in new window

0
 
geek_vjConnect With a Mentor Commented:
Please note that you can retrieve this information only from the available info from cache and there is no way to retrieve if the specified proc was flushed out of cache either by system or manually.

Here is the query:

declare @proc_nm sysname

-- select the procedure name here
set @proc_nm = 'SP name here'

select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id('AdventureWorks')) = @proc_nm

-- Ref: http://stackoverflow.com/questions/130753/last-time-a-stored-procedure-was-executed

Please understand that this might give partial results as it will pull information only from the current cache.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.