Who dropped that table?

Who dropped that table?  Who altered that view?  Who added that column.... and so forth.  You'd like to think you'll always know what's going on in your databases, but unless NOBODY touches it but you, that's just not possible.  I can tell you war stories that will floor you!  Some other time with a glass of red...  Until then, this is a quick query to tell you who dropped or altered any object within any database on your server.   

       -- declarations

       DECLARE

              @current VARCHAR(255),

              @start VARCHAR(255),

              @index INT;

       -- find your trace path

       SELECT @current = path FROM sys.traces WHERE is_default = 1;

       SET @current = REVERSE(@current)

       SELECT @index = PATINDEX('%\%', @current)

       SET @current = REVERSE(@current)

       SET @start = LEFT(@current, LEN(@current) - @index) + '\log.trc';

       -- query on the eventclasses for delete and alter

       SELECT CASE EventClass WHEN 164 THEN 'Object:Altered'

              WHEN 47 THEN 'Object:Deleted' END [Action],

              DatabaseName,

              ObjectName,

              HostName,

              ApplicationName,

              LoginName,

              StartTime

       FROM::fn_trace_gettable(@start, DEFAULT)

       WHERE EventClass IN (164,47)

       AND EventSubclass = 0

       AND DatabaseID <> 2

 Your end result will vary.. but this is my output from one of my instances:

Now you go talk to Joe Shmo or Ms. Lenovo and figure out why they're editing/dropping objects in your database.  😏

See these for details on fn_trace_gettable and the object events I used in my query:

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-trace-gettable-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/objects-event-category?view=sql-server-2017