Stored Procedures last access time?

Question

Is it possible to find out when a Stored Procedure was last accessed?

I tried the following:

SELECT * 
FROM sys.dm_db_index_usage_stats 
WHERE [database_id] = DB_ID()  
    AND [object_id] = OBJECT_ID('stored procedure name')

and it returns a blank resultset.

Answer 1

Add a log entry as the first line of the stored procedures:

insert into dbo.ProcLog (procname, date) values ('MyProc',getdate())

Answer 2

I believe this is possible should the sproc still be in the procedure cache on the server at which point you can query sys.dm_exec_query_stats.

After that you are down to logging and tracing I'm afraid.

Answer 3

here is a generic line of code you can place in every procedure, it will include the proper procedure name, without hard coding it.

INSERT INTO YourLog 
        (RunDate,ProcedureName,...) 
    VALUES 
        (GETDATE(),OBJECT_NAME(@@PROCID),...)