Last time a Stored Procedure was executed

sql-server stored-procedures sql-server-2000
Steven Williams picture Steven Williams · Sep 25, 2008 · Viewed 37.1k times · Source

On Sql Server 2000, is there a way to find out the date and time when a stored procedure was last executed?

Answer

Ben Hoffstein picture Ben Hoffstein · Sep 25, 2008

If a stored procedure is still in the procedure cache, you can find the last time it was executed by querying the sys.dm_exec_query_stats DMV. In this example, I also cross apply to the sys.dm_exec_query_plan DMF in order to qualify the object id:

declare @proc_nm sysname

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

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

[Source]

Related questions

SQL Server 2000: How to exit a stored procedure?

How can I exit in the middle of a stored procedure? I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on running: …

Read More
Check if record exists, if yes "update" if not "insert"

I want to check table PREMIUM_SERVICE_USER if any records exists for strClientID update timeValid for +30 if no records for strClientID insert to premium_service_user table. What am I doing wrong? It increases timeValid for +30 days but inserts …

Read More
SQL Error: Incorrect syntax near the keyword 'End'

Need help with this SQL Server 2000 procedure. The problem is made difficult because I'm testing procedure via Oracle SQL Developer. I'm running the procedure to iterate column with new sequence of numbers in Varchar format for those who have null …

Read More