SQL 2005 Stored Procedure Last Modified Date
Getting the last modified date for sprocs in SQL 2005 is really easy using the following query:
-- Queries the sys.objects system view to gather information
-- about user defined stored procedure database objects;
-- specified with type='P'.
-- SQL 2005 --
USE AdventureWorks; -- Database name
SELECT name, create_date, modify_date
FROM sys.objects -- User defined objects system view
WHERE type = 'P' -- Only return stored procedures
AND name LIKE 'mycompanyprefix_%' -- Only return sprocs with names beginning with "mycompanyprefix_"
This will return a list of all user defined stored procedures in a database along with their name, creation date & last modified date. This will only work if the ALTER command was used to update the sprocs, if the sprocs were dropped and re-created only the create_date will contain any useful information.
Unfortunately, there isn’t an equivalent command for SQL 2000. The modified date columns in SQL 2000 weren’t being updated correctly and from the research I performed Microsoft didn’t fix the problem until SQL 2005. I tried writing the following similar query to the one that works in SQL 2005, but the created date & ref date are always the same.
-- SQL 2000 --
-- DOESN'T WORK --
SELECT name, crdate, refdate
WHERE type = 'P'
AND name LIKE 'mycompanyprefix_%'
However, if you just need the created date for the sprocs you can get that using this query in SQL 2000.