If you are like me, then you HATE clutter. I am a minimalist - yep even when coding. I hate to see stored procs laying around that I:
1. Don't know what they do OR
2. Don't know if they are even in use
Of course, you don't want to start deleting procs randomly (that's a no-no)
So here are a few snippets to help you with your SQL house keeping:
This one tells you all of the procs that have run since the last restart.
SELECT p.name, last_execution_time, s.type_desc, create_date, modify_date
FROM sys.dm_exec_procedure_stats AS s
INNER JOIN sys.procedures AS p
ON s.[object_id] = p.[object_id]
ORDER BY p.name;
And this one shows procs that have NOT been run since last restart:
SELECT p.*
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS s
ON s.[object_id] = p.[object_id]
WHERE s.object_id IS null
order by name
Happy Cleaning!
