Requires Free Membership to View
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
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation