SQL server: List out last modified procedures and functions

I use a small query to find out last modified stored procedures, functions, views and tables from Sql Server.


SELECT name, create_date, modify_date,type
FROM sys.objects
WHERE type IN ('FN','V','P','U','TF')
AND DATEDIFF(D,modify_date, GETDATE()) < 6
ORDER BY modify_date


Here are different types used in IN clause in WHERE condition:

  • FN – Scalar valued function
  • V – View
  • P – Stored Procedure
  • U – Table
  • TF – Table valued function

AND DATEDIFF(D,modify_date, GETDATE()) < 6 – It will show the objects modified in last 6 days