1

Using SQL Server 2005 I want to see when a certain procedure has been modified. With this query I get the last modified date:

SELECT name, create_date, modify_date
FROM sys.objects
WHERE name = 'name_of_proc'

I need to get when it was modified before that or preferably a list of all modifications. Is that possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mikael Härsjö
  • 1,048
  • 6
  • 14
  • You have [source control](http://stackoverflow.com/a/1597085/27535) or http://stackoverflow.com/a/4524616/27535? – gbn Feb 06 '12 at 08:03

3 Answers3

1

No, not possible. SQL Server does not keep a history of all DML operations somewhere in memory for the abstract case one of ten thousand admins wants to see it once in a year.

TomTom
  • 61,059
  • 10
  • 88
  • 148
1

You could try the Schema Changes History report from SQL Server Management Studio.

Right click on database - Reports - Standard Reports - Schema Changes History

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • IIRC, this relies on the default trace. If so, your history is limited by how many of those files you have laying around. – Ben Thul Feb 06 '12 at 13:06
1

You could always set up a DDL trigger for CREATE_PROCEUDURE and ALTER_PROCEDURE and store the results in a table. If you capture the actual DDL run, it has the advantage of serving as an archive of the actual procedure as well. Which is to say if you need to roll it back to a previous version, knowing what it was changed from will be of particular use.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • This is how to create the DDL trigger to log changes But before this, you need to create a table and save all existing stored procedure http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/ – RAY Sep 26 '14 at 02:43