Q

Date stored procedure was last modified

Is there a way to grab the date a stored procedure was last modified? I can get the date created, but not modified.

In all SQL Server databases, both system and user defined, a table by the name of sysobjects stores the creation

date among other data for all objects in the database. Unfortunately, the only value that is captured in any of system tables with respect to object dates is sysobjects.dbo.crdate, but not the last date/time modified that you are searching for. This date would be very beneficial to validate a new stored procedure has been released, but the sysobjects.dbo.crdate value is not updated. One way to have SQL Server reflect the updates is to not ALTER (PROCEDURE) any stored procedures, which occurs by default when using Enterprise Manager, but rather script out the stored procedures and execute the code in Query Analyzer with a DROP PROCEDURE and a CREATE PROCEDURE statement each time an object is released. This way the value in sysobjects.dbo.crdate will reflect the date/time stamp for the latest changes.

Hopefully the next version of SQL Server, SQL Server 2005, expected in the first half of 2005 will address this request. We shall see...

For More Information

This was first published in July 2004

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close