Find Recently Modified Stored Procedures
From Wiki
How can you easily find out which stored procedures have been changed in the last hour/week or whatever period you would like to use?
In 2000 there was no easy way because refdate and crdate were identical in the sysobjects table.
SQL Server 2005 introduced sys.procedures, sys.procedures has the columns create_date and modify_date. The columns modify_date does change when you alter a procedures. Let us take a look at how this works
First create this stored procedure
- CREATE PROC prtest
- AS
- SELECT GETDATE()
- go
Now run this query, as you can see the dates are the same
- SELECT name,create_date,modify_date
- FROM sys.procedures
- WHERE name = 'prtest'
- go
Now change the procedure
- ALTER PROC prtest
- AS
- SELECT GETUTCDATE()
- go
When you run the same query from before you can see that the modify_date column is not the same as the create_date column anymore
- SELECT name,create_date,modify_date
- FROM sys.procedures
- WHERE name = 'prtest'
- go
Here is a query which returns all the procs that have been modified in the last 60 minutes
- SELECT name,create_date,modify_date,DATEDIFF(mi,modify_date,GETDATE()) AS ModifiedMinutesAgo
- FROM sys.procedures
- WHERE DATEDIFF(mi,modify_date,GETDATE()) < 60
- go
Contributed by: --SQLDenis 17:30, 17 September 2008 (GMT)
Part of SQL Server Admin Hacks
