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

This post is second part of my previous post about SQL SERVER – 2005 – 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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

99 thoughts on “SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

  1. i ran this command and it says -invalid column -created_date and invalid modified_date.
    also sys.objects is sysobjects.
    pls let me know the correct syntax to find modified date of SP

    • if you read the post it states create_date, modify_date

      your relpy says invalid column created_date and invalid modified_date.

      created_date != create_date && modified_date != modify_date

  2. I don’t know about the first poster, but I ran this command just fine. Copy & paste…replaced the stored procedure name and the information came up just fine.

  3. @Jake,

    I agree with you.
    I think first poster might be talking about SQL Server 2000. As mentioned in post title it is for only SQL Server 2005.

    @radheshyam patel,
    The syntax is correct for SQL Server 2005. I only provide correct syntax. Also sys.objects is correct syntax for SQL Server 2005.

    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  4. I need to take the complete modification history of a stored procedure. Sysobjects hold only last modified date. But i need history of Stored procedure every time i modifies.
    can u give me a solution.

  5. Hi Dave,

    I am using SQL2005 Management studio express and try to find the select * from sys.objects then it displays Invalid object name ‘SYS.OBJECTS’.

    As I have to find the last modified date of any stored procedure i ma using the query:
    SELECT name, create_date, modify_date
    FROM sys.objects
    WHERE type = ‘P’ AND name = ‘sp_name’

    if I use sysobjects then there is no field as modify_date. there are two date fields crdate and refdate but both are same. even if I modify the procdure its values remains the same as crdate. Please suggest. its urgent. u can also tell me for SQL 2000.

  6. Hi…This will gonna work for SQL Server 2000

    USE pubs
    GO
    SELECT name, crdate, refdate
    FROM sysobjects
    WHERE type = ‘P’ AND name = ‘insert_authors’
    GO

  7. Hi,

    Would like to ask if in SQL Enterprise manager, I modified the table, will the “Create Date” time stamp change? What resources can I read regarding this?

    thanks

  8. Hey.

    In Oracle it is possible to see what stored procedures are valid by issueing a “compile” command. Is there any similar way in MS SQL Server? I think of the situation where a stored procedure is doing a SELECT on a column that later changes its name and hence all of a sudden will fail.

    I hope there is.

    Cheers

    Claus

  9. Hi,

    I think we can not able to identify the modified date of a procedure in sqlserver2000. we need to use tools to identify them. but in sqlserver2005 we can do by using the script given by dave.

  10. Hi,
    I want to write a stored procedure for knowing the server date(Current date). so please can you help me out regarding this, please.

    Thanks,

    gowtham

  11. i m not getting a right output.
    i want a procedure which returns a seach value a any name given to it from a table

  12. Hi all,
    Im using two database db1,db2, the data will be insert,update will done in db1 will be uploaded once in a day.if i updated or inserted the records in another day means it should fetch that records alone and updated to db2 database,im using sql server 2000,asp.net2.0.how can i acheive this one.

    Regrds
    Kanna

  13. thank u alot for that code
    it work just fine but i need to know when was the last modify date for the data in my database

  14. When i am executing the following query
    SELECT name, create_date, modify_date
    FROM sys.objects
    WHERE type = ‘P’
    it is showing error “Invalid object name ‘sys.objects'”

    in sql server 2005, Microsoft SQL Server Management Studio 9.00.1399.00

  15. please help me

    how to find sql objects like stored procedure,functions created client login name in server by using t-sql query

  16. I would like to capture the date on which a record is being generated
    for eg : this is a weekly report and one employee record came in the first week and when i run the report in the next week it should show me the last record running date.

    how can we get this since its record level capturing

    early response will be appreciated!

  17. do this for sql server 2000 for same result :

    —————————————————————————
    select *
    from information_schema.routines
    WHERE
    Routine_Type=’procedure’
    AND specific_name= ‘uspUpdateEmployeeHireInfo’
    AND specific_Catalog=’AdventureWorks’

    ————————————————————————–

    use specific_name for your determind stored procedure name
    and specific_Catalog
    for your determind database name
    good luck :)

  18. hi Navee, what is your database?

    hi Raj Acharya, you ca n use above query for this porupose if your databse is sqlserver2000, if you have any other database let me know.

  19. hi sir/madam,

    i write one select command in stored procedure
    Eg., Select * from employee_tb where employeeid=’50000001′

    above statement written in stored procedure.
    How to retrive the data in ASP .
    i want information employeefirstname ,lastname,badgeid,salary.

    how ‘ll you call above storedprocedure in ASP.
    finally i want information above 3 fields.

    Thanks,
    Jagadish

  20. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  21. Hi,

    I tried this syntax to find the last modified date of a table. But its showing same date for create and modify. Does this modification means change in table structure or modify means modification in data also? I also tried sys.tables to find the modification date but its showing same result? Is there any way to find the last data modification date in a table?

    Thanks & Regards
    Jitesh

  22. I switched SQL Server versions and forgot how I set up solution/projects which directed my Stored Procedures to my database. Had problems recreated the Solution/project in SQLExpress 2008. When executed individually my stored procedures where dropped into mystery default land. I’ve got a bunch of them there but can’t find them on the machine. Your USE command helped me to insure that I direct my stored procedure to the right database. I know I should take the time to learn how to DBA my SQL Server tool but Ive got enough learning all the .NET stuff righ now. Thanks!

  23. Hi,

    I tried to add stored procedure in

    ….\…..\Databases\….\Programmability\Stored Procedures

    and it asked to ‘save as’ into a folder but when i refresh the stored procedure i just save won’t show in the Stored Procedure list.

    Please help as a I am not a SQL guru.

    Thanks,

  24. hi

    I want 2 get alter sp name ..

    eg:

    I was alter a sp in 1 jan 2009 .
    I was execute the query
    (SELECT * FROM sysobjects WHERE type = ‘P’) it was got all the sp from db.. i want alter sps from jan to till date ..any know tel me

  25. hi

    I want 2 get alter sp name ..

    eg:

    I was alter a sp in 1 jan 2009 .
    I was execute the query
    (SELECT * FROM sysobjects WHERE type = ‘P’) it was got all the sp from db.. i want alter sps from jan to till date ..
    any body knows tel me the answer..

    adv tx..
    vaira

  26. ALTER PROCEDURE setuserlogin
    (
    @username varchar (50),
    @logintime datetime
    )
    AS
    UPDATE userprofile
    set logintime=@logintime,onlinestatus=1
    WHERE
    username=@username;

    if i exe this then 1 error is coming near ste userlogin
    please suggest something,so i can exe it.

  27. We have a calendar that we currently have to update the information each day. Is there a way to have the dates in the table update daily? This would make it easier so that we can just add the info in once.

    Regards,

  28. Dear pinal

    Everyday i face problem regarding upload modified procedure and table script.

    i know how to find modified tables and procedure. but its very boring process to generate the modified procedure and table script.

    I want tool or query which automatically generate the script of modified procedure and modified tables.

    i hope you will get the solution.

    Waiting for your reply..

    Thanks
    Shankar.

  29. Hi Pinal Dave,

    Just had a simple question:

    I want to place created_date and modified_date field in every(almost all) table of my database and want created_date to be filled when new data is entered and modified_date to be filled when update is made?

    can you give me ur suggestion on this?

    Thanks
    Sabnam

  30. Hello Sabnam,

    You can define the default value GETDATE() for create_date column but for modified_date column you will have to implement one out of following two methods:
    1. Create update trigger for each table to update the modified_date column.
    2. Modify your application to update the modified_date vlaue with each UPDATE statement.

    Regards,
    Pinal Dave

  31. Hi Pinal,

    How to locate store proecdure when I have more than 15 database in my SQL server and I have not idea that which database is having this Storeprocedure.

    I tried . . . Suppose i am using loop and @i variable comes to 9th Database_id ..with database base platform “Master”

    Declare @i smallint
    SET @i = 9
    Declare @Databasename varchar (50)
    SET @Databasename = (SELECT Name FROM sys.databases where database_id = @i)

    USE @Databasename — Error ‘Incorrect syntax near @databasename’
    SELECT name , create_date from sys.procedures where name like ‘%Emails%’

    You tell me.. why i am not able to use variable with key word “USE” in SQL.
    Secondly, Can you plz give me the way through which I can locate SP on my SQL server and can find out that database having this SP.

  32. One method is to script all the procedures to a file and do find

    Another method is running this query

    declare @sql varchar(max)
    set @sql=”
    select @sql=@sql+’ select ”’+name+”’ as database_name from ‘+name+’.sys.objects where name =”spGetAttendanceReport1” union all’ from sys.databases
    select @sql=substring(@sql,1,len(@sql)-9)
    exec(@sql)

  33. Hi,

    Is there anyway to find out when was the database last accessed/modified in SQL Server 2000?

    Tried the below but noluck..
    1) select * from master.dbo.sysdatabases
    2) select * from master.dbo.sysfiles
    3) sp_Helpfile

    I didnt look at the last accessed/modified date of the physicall database/log files.

    Any help..much appreciated..

    Thanks,
    Dan

  34. Thank you for this.

    One has to wonder why Modified Date is not a column in SQL Server 2005 Management Studio; a rather obvious piece of info to display I should think.

    Thanks again,
    Matthew

  35. you can use INFORMATION_SCHEMA.ROUTINES catalog view for finding out created date and last modified dates of DB Objects in SQL Server 2005 Management Studio. Like :

    SELECT ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES

    Thanks,
    Ruchi

  36. @Ruchi,

    I have tried that query, and it doesn’t work in 2000. I have modified a procedure to see if the last_altered data changed, and it doesn’t, it keeps showing the create date.

  37. dear all,

    i want to know a store procedure for auto generation of a date & time when the a data is added to the table.

    can any body knows the procedure

  38. Hi, Pinal

    Would you like to display a single stored procedures using
    almost all SQL queries.It will be very helpful for me to understand the structure of SP’s as well as their working with queries.

    Shipra Agarwal
    jaipur.

  39. i want to know a store procedure for auto generation of a date & time when the a data is added to the table.

    can any body knows the procedure

  40. Found this from another site – works fine in 2005

    SELECT name, modify_date FROM sys.procedures

    if you need to run the same against tables then :

    SELECT name, modify_date FROM sys.tables

  41. Hi,

    Is there any way to find which user altered the table last time?

    Will it be logged somewhere in SQL Server 2005?
    If yes, how to retrieve it?

    Thanks,
    KB

  42. Hi ,
    When ever i create a store procedure like

    ALTER PROCEDURE uspAddAddressType
    (
    @addressTypeName Varchar(30),
    @status varchar(30),
    @addedOn datetime,
    @addedBy varchar(30)

    )
    AS
    BEGIN

    INSERT INTO addressType
    ( addressTypeName,
    [status],
    addedBy,
    addedOn
    )
    VALUES
    ( @addressTypeName,
    @status,
    @addedBy,
    GETDATE()
    )
    END

    And Save next time when i see that procedure it display like
    ALTER PROCEDURE [dbo].[uspAddAddressType]
    (
    @addressTypeName Varchar(30),
    @status varchar(30),
    @addedOn datetime,
    @addedBy varchar(30)

    )
    AS
    BEGIN

    INSERT INTO addressType
    ( addressTypeName,
    [status],
    addedBy,
    addedOn
    )
    VALUES
    ( @addressTypeName,
    @status,
    @addedBy,
    GETDATE()
    )
    END

    Why it is happening. Any one can help me .

  43. Hi
    Dear All,

    Already project developed. So there is some changes in the project. So whenever we are altering the procedures it is saving in a separate .sql file but in the same procedure. why it is happening like that. But i want to alter the procedure which is already there in the project.Please solve my problem

  44. SELECT name, create_date, modify_date
    FROM sys.objects
    WHERE type = ‘P’ and modify_date between ’10-Jan-2011′ and getdate()

  45. Hi,

    can we modify system stored procedure on SQL server 2005 ?

    Thanks in Advance

    Thanks & Regards
    Jayant Dass

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

  47. 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?

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

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

  50. 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?

  51. Hello,

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

    Thanks

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

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

    • 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

  54. Pingback: SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name « SQL Server Journey with SQL Authority

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

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

  57. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s