SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Quick Audits of Schema Changes

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


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.

Leave a Comment

Please register or log in to leave a comment.