Is there a lastmodified date stored in sql server when a s_proc is changed?

Question

Post reply

Is there a lastmodified date stored in sql server when a s_proc is changed?

james powers

SSC Journeyman

Points: 93
More actions
August 23, 2006 at 2:01 pm

#114217

Is there a lastmodified date stored in sql server when a s_proc is changed?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

There is a creationdate in sysobjects. am not sure if you can find the "modified date".

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 2

try this

select

name,modify_date from sys.all_objects where type='P'

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 3

You can try this also

select

name,modify_date from sys.objects where type='P'

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

There is no "modify date" for schema changes in SQL Server 7, 2000, or 2005. You can however, add a new database trigger in SQL Server 2005 that will allow you to capture such changes including a modification date and the user that did it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 5

I think James needs to know the last modified date of a stored procedure

This can be get from INFORMATION_SCHEMA views in SQL Server 2005

"Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered."

This was the explanation found for the field modify_date in my last query

Correct me if I am wrong.

John Beggs SSCommitted Points: 1921 More actions · Answer 6

Regarding SQL 7/2000 (the forum we are in), I went through this dilema about 4 years ago. While there is a column in the system tables that would make you think it was the change date of the stored proc, it was not. After trying to use it and some pretty frustrating testing (at the time) I found the column was only changed on a CREATE, not an ALTER, and therefore useless.

Regarding SQL 2005, which the OP probably does not have, because of the forum he is posting in, DDL triggers (suggested by Jeff) have been added to help with this. I cannot say if the system tables will have the correct data in them, as I have not done the research, but it really doesn't matter. Try using the DDL triggers, which will be the MS recommended/supported way to do this first.