-- Get list of all SPs in the current database (SQL 2005 and 2008)
SELECT p.name AS 'SP Name', p.create_date, p.modify_date
FROM sys.procedures AS p
WHERE p.is_ms_shipped = 0
ORDER BY p.name;
-- Get list of possibly unused SPs (SQL 2008 only)
SELECT p.name AS 'SP Name' -- Get list of all SPs in the current database
FROM sys.procedures AS p
WHERE p.is_ms_shipped = 0
EXCEPT
SELECT p.name AS 'SP Name' -- Get list of all SPs from the current database that are in the procedure cache
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.object_id = qs.object_id
WHERE p.is_ms_shipped = 0;
You can get only Last Modified date for unused procedures....
For recently used procedures last_execution_date is available in cache procedure sys.dm_exec_procedure_stats
--
Chandu