How to see when stored procedures have last run

I want to see a listing of all the stored procs for each database on a server along with when the last time that store proc was run. I'm pretty good with SQL but I don't know about looking at stats like this that sql keeps so I'd appreciate a little help finding this info.

EDIT:

From the answers I'm getting it sounds like this is not possible the way I thought it would be. I was thinking that it could be done similarly to how you can see when a table was last accessed:

select  t.name, user_seeks, user_scans, user_lookups, user_updates, 
        last_user_seek, last_user_scan, last_user_lookup, last_user_update

from    sys.dm_db_index_usage_stats i JOIN
        sys.tables t ON (t.object_id = i.object_id)

where   database_id = db_id()

The above script was stolen from a comment on http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/.

Solution 1:

Here is a little script that will list all of the user stored procedures (ie- those that didn't come with SQL Server or aren't system stored procedures) in all of the databases on your server instance:

CREATE TABLE #ProcTable (
    DbName sysname,
    ProcName sysname
)

DECLARE @command1 NVARCHAR(1000)

SET @command1 = 'USE [?];INSERT #ProcTable 
SELECT ''[?]'',Name
FROM sys.all_objects
WHERE TYPE=''p''
AND is_ms_shipped=0'

EXEC sp_msforeachdb @command1

SELECT * FROM #ProcTable 
ORDER BY DbName,ProcName

DROP TABLE #ProcTable 

If you're not running a trace that will capture the exec event (and don't have a default trace running) then you'll have to have an application level auditing mechanism in place to capture the running of stored procedures. If you don't have either of those going then you're not going to be able to go back in time to see when the procs have run.

Solution 2:

There's no native logging which does this, so you'll need to either run a server side trace or setup some sort of auditing to capture this information.

Solution 3:

You can get a limited answer to that question by looking at the sys.dm_exec_procedure_stats dynamic management view that was introduced in SQL Server 2008.

select
    DB_NAME(database_id) as "database_name" 
    , OBJECT_NAME(object_id, database_id) as "procedure_name"
    , last_execution_time
from 
    sys.dm_exec_procedure_stats

For any stored procedure plans that are in the procedure cache, this will tell you the last execution time. (There are many more interesting columns in the DMV as well.)

This information isn't necessarily complete: at best it will have information only since the last start of the database engine (not persisted across service restarts.) If a stored procedure hasn't been run since the server was restarted, it won't be present here. Clearing the plan cache will cause this DMV to be reset. As will operations that clear the plan cache as a side effect (such as changing MAXDOP, or memory settings) or otherwise invalidate cached plans.

But it gives you a ballpark, and if you're feeling really ambitious you could write a routine that polls the DMV periodically and stores the results in a table for longer-term analysis.