Wednesday, July 9, 2008

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

3 comments:

Cristina said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Kaylee

http://www.thinkpadonline.info

Thant said...

Hi Aaron,

Nice post!
The following is the thing I did in SQL 2000. But the last modified date is not correct at all times, too.

SELECT ROUTINE_NAME,CREATED,LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
ORDER BY LAST_ALTERED DESC

Regards,
Thant Zin (UCSM)

Anonymous said...

die Termingemäße Antwort cialis generika nebenwirkungen cialis preise [url=http//t7-isis.org]viagra ohne rezept[/url]