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.
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)

, , ,
Previous Post
SQL SERVER – 2005 – List All The Column With Specific Data Types
Next Post
SQL SERVER – UDF – Validate Integer Function

Related Posts

100 Comments. Leave new

  • Hi,
    how to find stored procedure change user detail and column ınformation situation

    Thanks

    Reply
  • Thank you so much..

    Reply
  • 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
  • Hi how do i get sql loginname along with the modified date of an object

    Reply
  • 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
  • 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
  • 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

Leave a Reply

Menu