I needed a script that showed the last change in time for the stored procedures in my database so I came up with the following. Certain of you may find it useful. Please comment if you have ideas for improvements.

–sp audit by Terry Grignon; for SQL Server 2005 and possibly newer versions
SELECT COUNT(DISTINCT modify_date) as [SP's changed],
datepart(yyyy,modify_date) as ModificationYear,
datepart(dy,modify_date) as ModificationDayOfYear,
min(modify_date) as [First Change],
max(modify_date) as [Last Change]
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
group by datepart(yyyy,modify_date), datepart(dy,modify_date)
order by datepart(yyyy,modify_date), datepart(dy,modify_date);

SELECT name, create_date, datepart(dy,create_date) as CreatedDayOfYear,
modify_date, datepart(dy,modify_date) as ModificationDayOfYear
FROM sys.sql_modules
JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id
AND TYPE = ‘P’
order by datepart(yyyy,modify_date) asc,
datepart(dy,modify_date) asc, name;