I saw someone post a question recently about tracking down who deleted a stored procedure. I suggested the default trace, and linked to a beginner article on it.

However there was another post that had a better way to track things. In SSMS there’s a report that will help you:

Right click a database, and drill down as shown above through reports to the “Schema Change History” report and select it. Previously I had issued this:

ALTER TABLE aa ADD bb varchar(20)

And when I ran my report, I found this in the list:

It doesn’t give the code, but it says that I altered the proc today.

I then tested for stored procedure. I created, and then deleted a quick proc, so it only existed on my system for a few minutes. When I ran the schema report again, despite this proc no longer being around, it was there:

This comes off the default trace, so there’s only a limited amount of data kept. If you catch something quick enough, however, you can easily track down some information about it.