SQL – How To See Last Modified Date of Objects in SQL Server 2005

A handy tip for those of you not yet all too familiar with SQL Server 2005 and getting lost looking for an option to show them the last modified date of -for example- a stored procedure. There is none ! Just execute below T-SQL in the context of your database.

SELECT
[name],
create_date,
modify_date
FROM sys.procedures
WHERE
[type] = 'P'
AND is_ms_shipped = 0
AND [name] NOT LIKE 'sp[_]%diagram%'
ORDER BY
modify_date DESC

Obviously, you can do the same for tables, using sys.tables:

SELECT
[name],
create_date,
modify_date
FROM sys.tables
ORDER BY
modify_date DESC