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)
100 Comments. Leave new
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
Its create_data and modify_date and not created_date and modified_date.
Above command works fine :)
Hi Guys i have checked this query.. and this worked poperly…
This guy have great knowledge of Sql.
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.
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.
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.
Hi Yoganand,
Any updates. How did you get the complete modification history of a Stored procedure.
Rashmi
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
Refdate is not equivalent of SQL Server 2005’s modify_date
I wanted to select only the procedures in a database. How can I write the select statement?
is refdate is modified date in sqlserver2000
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
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
Alter the stored procedure
Hi,
Yes, it shows same date but i have made changes in stored procedure.
Salamat po!
Dear Sir…
can i know the stored proc for altering date.(current system date)
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.
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
Select getdate()
my database is having windows authentication i need to know who modified my stored procedure
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
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
Thanks for the query! Worked great!
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