1/29/2016

Recently Executed Stored Procedures

I put together the following in order to find a distinct list of the most recently executed stored procedures for auditing (reverse engineering) purposes:

SELECT R.specific_name,
       R.created,
       R.last_altered,
       Max(s.last_execution_time) AS [Most Recent Execution]
FROM   sys.dm_exec_query_stats s
       CROSS apply sys.dm_exec_sql_text (s.plan_handle) p
       JOIN information_schema.routines R
         ON Object_name(p.objectid, Db_id('[DB_NAME]')) = R.specific_name
WHERE  Object_name(p.objectid, Db_id('[DB_NAME]')) IS NOT NULL
       AND R.routine_type = 'PROCEDURE'
       AND p.encrypted = 0
GROUP  BY R.specific_name,
          R.created,
          R.last_altered
ORDER  BY R.specific_name 


*Careful, it can take a while in prod.

No comments:

Post a Comment