Is it possible to determine when a stored procedure was last modified in SQL Server 2000?

Question

I know that you can do this in SQL Server 2005, but I'm at a loss for 2000.

Answer 1

From all the research I've done on this in the past, I unfortunately have to say no. SQL Server 2000 simply does not store this information, and I've never seen any solution for retrieving it.

There are a few alternative methods, but they all involve user intervention. Besides keeping stored procedure scripts in a source control system, I think the next best approach is to use comments inside the stored procedure. Not ideal, but it's better than nothing if you want to track what gets updated.

Answer 2

Not to my knowledge.

To get around this, I manage my stored procedures in a Visual Studio database project. Every stored procedure is in its own file and has a drop command at the top of the file. When I update the stored through Visual Studio, the database's created date is updated in the database because of the drop/create statement. I am able to use the created date in SQL Server 2000 as the last modified date in this manner.

Answer 3

SELECT crdate FROM sysobjects WHERE name = 'proc name here' AND type = 'P'