Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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:

auditreport

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:



auditreport2



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:



auditreport3



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.

Comments

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.