Who touched that table? !

Question

Who touched that table? !

Barkingdog

SSCoach

Points: 18670
More actions
October 24, 2007 at 9:14 am

#126299

Every now and then we wonder which developer modified the schema of a certain table. (We haven't enabled auditing). Is there a system table (e.g. sysobjects) that could provide us the answer? If not, is there any other way to find out?
TIA,
Barkingdog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 1

I'm not sure about system tables but you can create DDL triggers in 2005. So you can easily create a trigger that runs any time a table is created, modified etc and store the information and even the code they ran into a logging table you create somewhere.

Another option is to create an on going trace or series of traces that just check for DDL events. The triggers will be easier to implement though.

Kenneth Fisher

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

If it didn't happen too long ago, you can see a history of schema changes in a database by using the "Schema Changes History" report on the Summary tab in SSMS.

Click on the database in SSMS and press F7 to bring up the Summary page. Pull down the Report list and choose "Schema Changes History".

You don't get as much information as you do from a DDL trigger, but it's good for a quick look.

Greg

K. Brian Kelley SSC Guru Points: 114703 More actions · Answer 3

In the LOG directory, there should be SQL Profiler traces which are created by default with SQL Server 2005 to track this sort of thing. You might be able to extract the information out of them.

K. Brian Kelley
@kbriankelley

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 4

Barkingdog (10/24/2007)
Every now and then we wonder which developer modified the schema of a certain table. (We haven't enabled auditing). Is there a system table (e.g. sysobjects) that could provide us the answer? If not, is there any other way to find out?
TIA,
Barkingdog

As some have said, you can add a schema trigger to the table... but, I've gotta ask... your developers can modify tables in production>:blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)