How to Find the Last Modified Date of Stored Procedures in SQL Server Instance

SELECT name, create_date, modify_date

FROM sys.objects

WHERE type = 'P'

ORDER BY modify_date DESC

This query will return a list of all stored procedures in the current database, along with their creation date and the date of the most recent modification. The "sys.objects" system catalog view contains metadata about all objects in the current database, including stored procedures. The "type = 'P'" condition filters the results to only include stored procedures.

Find the date of the most recent modification for stored procedures:-

SELECT [name], create_date, modify_date
FROM sys.procedures
ORDER BY 3 DESC;

You can also add a WHERE clause to limit the results to only those changes that have occurred in the past week, month, year etc.

SELECT [name], create_date, modify_date
FROM sys.procedures
WHERE [name] IN (SELECT [name]
FROM sys. procedures
WHERE   (DATEDIFF (d, create_date, GETDATE ()) = 1)
OR (DATEDIFF (d, modify_date, GETDATE ()) = 1))
ORDER BY 3 DESC;

use sys.object table instead of sys.procedures to find the date;

SELECT [name], create_date, modify_date
FROM sys.objects
WHERE type = ‘P’ –to only display ‘SQL_STORED_PROCEDURE’
ORDER BY 3 DESC;