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)

SQL Scripts, SQL Server, SQL Stored Procedure, SQL System Table
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

  • @Triveni….

    its ok……….

    Reply
  • thanks FOR this post ….i REALLY NEEDED IT

    Reply
  • Jayant dass
    March 6, 2011 1:22 pm

    Hi,

    can we modify system stored procedure on SQL server 2005 ?

    Thanks in Advance

    Thanks & Regards
    Jayant Dass

    Reply
  • declare @day as int
    set @day = 12
    select * from
    (SELECT [name],create_date,modify_date,’table’ type1,1 order1 FROM sys.tables
    union
    SELECT [name],create_date,modify_date,’view’ type1,2 order1 FROM sys.views
    union
    SELECT [name],create_date,modify_date,’trigger’ type1,5 order1 FROM sys.triggers
    union
    SELECT [name],create_date,modify_date,’sp’ type1,3 order1 FROM sys.procedures
    WHERE [type] = ‘P’ AND is_ms_shipped = 0 AND [name] NOT LIKE ‘sp[_]%diagram%’
    union
    select [name],create_date,modify_date,’fn’ type1,4 order1 from sys.objects where type_desc like ‘%function%’)
    as modify_table
    where datediff(dd,modify_date,getdate())<@day

    ORDER BY order1,modify_date DESC

    Reply
  • Jayant Dass
    May 13, 2011 5:14 pm

    Hi Mukund ,

    My Question is little different , can we alter system stored procedure on MSSQL server 2005 ?

    Thanks in Advance

    Regards
    jayant dass

    Reply
  • How do i get the user id of the person who altered store procedure along with modified date in SQL2000/2005/2008?Also how to configure SQL server for keeping the version history of SP changes?

    Reply
  • Dear All,

    I want to keep the history of any updations take place for any stored procedure ? not only the last modified date.

    Suppose a SP ‘ABC’s definition has been modified 10 times from the creation date; i required the data of 10 times updation. If it comes with client machine id; it will be a great helpful to track the activities done on SP.

    Reply
  • HI I am trying to create procedure with two columns and need perameter for Bins (if i select 30 then should dispaly 30, if I select 40 then should dispaly 40 ) for bin column. second column display all standard values up to 1000 rows like blow.

    Bins standard_normal_Values

    1 -0.999325493
    2 -0.99367071
    3 -0.986924533
    4 -0.982329311
    5 -0.97467364
    6 -0.970499059
    7 -0.969153007
    8 -0.968298082
    9 -0.958441433
    10 -0.958200417
    11 -0.958079909
    12 -0.957231805
    13 -0.955901669
    14 -0.954814823
    15 -0.954814823
    16 -0.95192263
    17 -0.950481081
    18 -0.950360572
    19 -0.950360572
    20 -0.947120498
    21 -0.94448751
    22 -0.94448751
    23 -0.943175564
    24 -0.94031293
    25 -0.938766789
    26 -0.938055109
    27 -0.937936875
    28 -0.936749984
    29 -0.935681328
    30 -0.93107019
    -0.928475856
    -0.923425887
    -0.922020718
    -0.921670562
    -0.918166734
    -0.913744316
    -0.910729341
    -0.909919891
    -0.907375579
    -0.906684363
    -0.904262834
    -0.903917226
    -0.901159183
    -0.899208317
    -0.896231995
    -0.895317953
    -0.894747245

    Reply
  • [name removed as it was cell number]
    July 27, 2011 5:48 pm

    last update date find problem in sql [email removed]

    Reply
  • I noticed that the modify_date on all of my objects is referencing the date we upgraded from SQL 2005 to SQL 2008R2. Is there any other column I could use to determine the last revision date for an object that would not have changed as a result of the upgrade?

    Reply
  • Hello,

    I want generate all the scripts of the tables and stored procedures modified between two dates. How can I do this?

    Thanks

    Reply
  • THIS SHOWING SOME ERROR PLEASE DO THE FAVOUR TO SHOOT THIS

    CREATE PROCEDURE INCREASE

    DECLARE
    @INCR DATETIME

    AS

    @INCR=SELECT MAX(MonthDate) AS MDATE FROM VMONTHDATE
    @INCR=@INCR+1
    INSERT INTO VMONTHDATE(MonthDate) VALUES (@INCR)
    GO

    Reply
  • Maria Loretta Escobal
    September 21, 2011 3:41 pm

    How will i know the SQL Account used to modify the stored procedure?

    Reply
  • Hi,

    purchase table sales table
    PID PQty PID SQty
    1 10 1 5
    2 5 2 3
    3 7 1 4
    1 5

    Output Should be
    PId PQty SQty Remaining Qty
    1 15 9 6
    2 5 3 2
    3 7 0 7

    Can any one help me to get this output
    in a single query

    Reply
    • select distinct PID
      , (select SUM(pqty) from purchase where pid = main.pid) PQty
      , isnull((select SUM(sqty) from sales where pid = main.pid), 0) SQty
      , (select SUM(pqty) from purchase where pid = main.pid)
      – isnull((select SUM(sqty) from sales where pid = main.pid), 0) [Remaining Qty]
      from purchase main

      Reply
  • Any chance to find out the login id who created the stored procedure ?

    Reply
  • I just have to say, that I love your blog. I use it frequently to get information.
    Thank you.

    Reply
  • Great. This also Working For me too…. Thanks

    Reply
  • Hi,
    To find the stored procedure details
    “use DataBase_Name
    go
    sp_helptext StoredProcedure_Name”

    wil get the Complete Stored Procedure details

    Reply
  • Avinash Ranjan (@avinash_t)
    December 14, 2012 12:48 pm

    Very Vary Helpful …

    Thanks SQL Authority

    Reply
  • Thanks

    Reply

Leave a Reply

Menu
Exit mobile version