Subscribe to this blog
Briefcase
Print
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.
Posted by Hugo Shebbeare on 25 May 2010
Kewl, thanks!
Posted by Jason Brimhall on 25 May 2010
Thanks Steve for sharing - this is a good report.
Posted by Steve Jones on 25 May 2010
Glad you like it. This beats me running queries off the default trace. I was surprised when someone pointed it out to me.
Posted by Ashish on 31 May 2010
Good thing that I have learnt today.