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
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: