SQL Stored Procedure Modified Date Query

While working on a problem I wanted to determine the last modified date for stored procedures (sprocs) in the SQL database I was working in. After some Google’ing I learned the following, from forums, multiple blog posts, etc. I figured it would be nice to have this information in a centralized area, so here you go....

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

GO

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_"

GO

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.

sys.objects documentation:
http://msdn.microsoft.com/en-us/library/ms190324.aspx

SQL 2000

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 --

USE Northwind;

GO

SELECT name, crdate, refdate

FROM sysobjects

WHERE type = 'P'

AND name LIKE 'mycompanyprefix_%'

GO

However, if you just need the created date for the sprocs you can get that using this query in SQL 2000.

Aaron
http://www.churchofficeonline.com