Problem solve Get help with specific problems with your technologies, process and projects.

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

Dig Deeper on SQL Server Stored Procedures

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.