SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

This post is second part of my previous post about List All Stored Procedure Modified in Last N Days

This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.

USE AdventureWorks;
GO
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'uspUpdateEmployeeHireInfo'
GO

Now if you run above SP in SQL Server Management studio, you will get following result set. In the result set you can clearly see that it now the query is displaying the name of the stored procedure along with created date as well as modified date. This may be very useful for internal audit as well as if you like to keep watch on your objects.

In recent times at one of the organizations I have noticed that they had created a job to check the stored procedure modification date and if they see changes in the stored procedure date they will create a report based on it. This particular behavior helped the organization to keep track on the object modification and source control.

Leave a comment below this post and tell us if you have seen ever such situation where you have to keep track of the SP modification date?

Reference: Pinal Dave (https://blog.sqlauthority.com)