Get last modified stored procedures on SQL 2005 and above

If you need to quickly get the list of all last modified stored procedures and the body of the stored procedure to aid in a quick update deployment from pre staging to production environment, try this stored procedure.

Create proc DevGetLastModifiedItems
(
  @AfterTimeStamp as datetime
)
as
  declare varCur cursor for 
    Select sys.objects.name, modify_date,
           sys.all_sql_modules.definition
      from sys.objects
           inner join sys.all_sql_modules on sys.objects.object_id = sys.all_sql_modules.object_id
      where sys.objects.type='P'
        and sys.objects.modify_date >= @AfterTimeStamp
        and sys.objects.name <> 'DevGetLastModifiedItems' -- Cannot see why this SP should ever turn up on a production server :)
      order by modify_date desc
  open varCur
  declare @name as varchar(200),
          @definition as varchar(max),
          @modify_date as datetime
  Fetch next from varCur into @name, @modify_date, @definition
  while @@Fetch_status = 0
  begin
    Print '-- ' + @name + ' last modified on ' + convert(varchar(40), @modify_date, 9)
    Print ''
    Print @definition
    Print 'go'
    Print ''
    Print ''
    Fetch next from varCur into @name, @modify_date, @definition
  end
  close varCur
  deallocate varCur
go

To get the complete list and body of the stored procedures that have been modified since a date say '2010-11-04', simply fire this on the Management Studio window and check out the contents of the messages window

exec DevGetLastModifiedItems '2010-11-04'