locked
Foreign Constraint dropped from a table; RRS feed

  • Question

  • Hi,Someone dropped two foreignkey constraint on our database table. The table was replicated to another database and we were experiencing issues till we recreated the constraints, I looked at the database and found no one had dbo other than the DBA's, is there any way to find when/who dropped the constraint? We dont have any 3rd party tools installed on that server.
    Thanks
    Friday, December 18, 2009 8:37 PM

Answers

All replies

  • If you are running on SQL Server 2005 or 2008 and have the Default Trace enabled, it is tracing Object Created, Object Deleted, and Object Altered events. 

    Load the trace into a table so that you can search for ObjectID = Object_ID (YourTableName).   The row should identify the HostName, LoginName, and StartTime of the event.  (It will not contain the text of the code, since it is a lightweight trace, but that may get you close.

    A couple of pages on the general subject:
    http://www.eraofdata.com/blog/2009/09/the-sql-server-default-trace/
    http://blogs.technet.com/beatrice/archive/2008/04/29/sql-server-default-trace.aspx

    RLF
    Friday, December 18, 2009 9:08 PM
  • Thanks, I queried the default trace, Its not returning any records? Is it possible the data is not there in the default trace?
    Thank you
    Friday, December 18, 2009 11:22 PM
  • If you have default trace enabled , then right click the server and go to reports and see SCHEMA CHANGES HISTORY / object deleted reports from that .
    Thanks, Leks
    Friday, December 18, 2009 11:55 PM
    Answerer