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

August 10, 2007 by pinaldave

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://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Cursor, SQL Documentation, SQL Download, SQL Error Messages, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, T SQL, Technology | 26 Comments

26 Responses to “SQL SERVER - 2005 - Find Stored Procedure Create Date and Modified Date”

on August 14, 2007 at 2:47 am radheshyam patel

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
on August 21, 2007 at 3:10 pm Jake

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.
on August 21, 2007 at 4:08 pm pinaldave

@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)
on October 12, 2007 at 7:30 am Yoganand.A

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.
on November 13, 2007 at 12:43 pm Mithilesh

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.
on November 19, 2007 at 3:34 pm Rashmi

Hi Yoganand,
Any updates. How did you get the complete modification history of a Stored procedure.

Rashmi
on November 20, 2007 at 9:32 pm Rushabh

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
on November 27, 2007 at 1:10 pm Raghunath

I wanted to select only the procedures in a database. How can I write the select statement?
on December 6, 2007 at 5:33 pm Om Shanker Mishra

is refdate is modified date in sqlserver2000
on December 6, 2007 at 10:23 pm Jake Estorque

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
on December 12, 2007 at 2:54 pm Claus

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
on January 22, 2008 at 7:14 am Sukh

Hi,

Yes, it shows same date but i have made changes in stored procedure.
on February 5, 2008 at 2:58 pm Pinoy Abe

Salamat po!
on February 11, 2008 at 5:16 pm preethi

Dear Sir…
can i know the stored proc for altering date.(current system date)
on March 11, 2008 at 10:36 am Deivendra padmanaban

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.
on March 31, 2008 at 11:08 am gowtham

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
on May 16, 2008 at 10:39 am MLC

my database is having windows authentication i need to know who modified my stored procedure
on May 27, 2008 at 3:51 pm md mashooque ali

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
on July 23, 2008 at 12:55 pm Kannabiran

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
on July 30, 2008 at 10:44 pm Don

Thanks for the query! Worked great!
on August 6, 2008 at 3:10 pm ramah

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
on September 2, 2008 at 4:48 pm ved

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
on September 5, 2008 at 11:47 am Mayank Parmar

Well this is excellent, I use this whenever I have to upload new changes to my live site.

Thanks & Regards,
Mayank
on November 11, 2008 at 3:05 pm Vijayakumar.P

please help me

how to find sql objects like stored procedure,functions created client login name in server by using t-sql query
on December 12, 2008 at 10:28 am Prasanti

Can we get the Previous version(Before Modification) Script of Modified Stored Procedure in SQL SERVER 2005.
on December 23, 2008 at 6:09 pm Raj Acharya

Hi,

i want to find out last access date of any procedure or function can u help me out from this