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?
Answer by Paul Ward ·
This will get you half way there:
Use MyDatabase Go select b.name, a.last_execution_time from sys.dm_exec_procedure_stats a inner join sys.objects b on a.object_id = b.object_id where DB_NAME(a.database_ID) = 'MyDatabase'
It will tell you the last time a stored proc was executed.
As for who ran it, as far as I know that is not recorded. You could use profiler to recored this, but i wouldn't do that on production server.
Answer by SteveArmistead ·
Answer by Laerte Junior ·
SELECT a.execution_count , OBJECT_NAME(objectid) Name, query_text = SUBSTRING( b.text, a.statement_start_offset/2, ( CASE WHEN a.statement_end_offset = -1 THEN len(convert(nvarchar(max), b.text)) * 2 ELSE a.statement_end_offset END - a.statement_start_offset)/2 ) , b.dbid , dbname = db_name(b.dbid) , b.objectid , a.creation_time, a.last_execution_time, a.* FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b WHERE OBJECT_NAME(objectid) = 'YOURPROCEDURE' ORDER BY a.last_execution_time DESC