On Sql Server 2000, is there a way to find out the date and time when a stored procedure was last executed?
Open the desired log file with SQL Server Profiler. You can see the stored procedure name in the ObjectName column and the name of the user who modified the stored procedure name in the LoginName column.
Return Value in SQL Server Stored Procedure In default, when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The 0 value indicates, the procedure is completed successfully and the non-zero values indicate an error.
The type_desc column includes the object type while the execution_count column shows the number of times the stored procedure has been executed since it was last compiled. This can be useful information when researching performance issues.
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]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With