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