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

August 10, 2007

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.
SQL SERVER - 2005 - Find Stored Procedure Create Date and Modified Date storedprocedure-listing

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)

SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table

SQL SERVER – 2005 – List All The Column With Specific Data Types

SQL SERVER – UDF – Validate Integer Function

100 Comments. Leave new

Manish Solanki
March 8, 2013 6:44 pm
Hi Friends…
I have Problem with Date Field.
I have Birthday_Table.
Table Filed :
=========
ID(int),
NAME(varchar(150)),
DOB(date)
Store Procedure:
=============
//Store Procedure Parameters
@ID INT,
@NAME VARCHAR(150),
@DOB DATE
INSERT INTO Birthday_Table
(ID,NAME,DOB)
VALUES
(@ID,@NAME,@DATE)
I Don’t wants to change DOB datatype.
if i pass DOB parameter like ’05-05-2013′ then Execute Successfully but
i pass DOB parameter like 05-05-2013 then prompt me error in ‘-‘.
Please friends help me….
I hope I will get it from hear..
Reply
Grgegory Ferdinandsen
March 14, 2013 5:30 pm
I use, that way the code is more generic and does not require know the name of the SP
select @SPCreate = create_date from sys.objects where name = @SPName
select @SPMod = modify_date from sys.objects where name = @SPName
Reply
suraj
January 7, 2014 2:38 pm
Hi how do i get sql loginname along with the modified date of an object
Reply
Svm
April 18, 2014 3:35 pm
Hi
is any way to stored last modify date inside sotred procedure. Becouse when i right click > modify in stored procedure there is:
/****** Object: StoredProcedure [dbo].[GetFxOrder] Script Date: 2014-04-18 12:03:54 ******/
Is any way to store there last modify date there?
Reply
seyed jafar
July 12, 2015 11:44 am
Hi
i have some data in my database (table A) which include (Product_Date and Expire_Date) entity.
now i want when Expire_Date equal with Date Now(Today Date) system show some warning Form or alarm .
i want to do it in Store procedure how i must do it.
what is code.
Reply
- madhivanan
  July 13, 2015 5:59 pm
  This will exclude all the expired data
  where expire_date<getdate()
  Reply
seyed jafar
July 12, 2015 11:46 am
hi
continue about last my Question .
also i want take report about Expire_Date.
i want do it in store procedure .
what is code.
thank you.
Reply