Find the SQL query that caused an event in the past I have a database in which I noticed that one of my tables is missing. 

I haven't used this database for over three months and now I want to find out who caused this, when this happened, how it happened and what code was used (or generated by SQLMS) for this action.

How can I do that?
This data might still be in the default trace, which can roll over and phase out files based not only on activity but also based on service restarts (so I say it might be there because you may have restarted SQL Server more than 5 or 6 times in three months, even if there hasn't been a lot of activity). 

Anyway, it is one of the examples I included in this answer, which I'll repeat a portion of here:


  Question: Who deleted the dbo.EmployeeAuditData table and when?


This will return any DROP events for an object named EmployeeAuditData. If you want to make sure that it only detects DROP events for tables, you can add a filter: ObjectType = 8277 (the full list is documented here). If you want to restrict the search space to a specific database, you can add a filter: DatabaseName = N'db_name'.

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName,
  HostName,
  StartTime,
  ApplicationName
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;


Please also see the other answer for additional caveats (e.g. details around the lack of recording of the schema of an object that has been dropped, and also the fact that TextData will never be populated - not that it will ever be anything varying far from DROP TABLE dbo.whatever;).