Use the below script to get the details of deleted/dropped databases. We can explicitly specify the name of the database which was deleted or get the output for all databases.

use tempdb

go

 

declare @enable int

select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = ‘default trace enabled’

if @enable = 1 –default trace is enabled

begin

declare @d1 datetime;declare @diff int;declare @indx int ;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @temp_trace table (obj_name nvarchar(256) collate database_default,database_name nvarchar(256) collate database_default,start_time datetime,

              event_class int,event_subclass int,object_type int,server_name nvarchar(256) collate database_default,login_name nvarchar(256) collate database_default,

              application_name nvarchar(256) collate database_default,ddl_operation nvarchar(40) collate database_default);

 

select @curr_tracefilename = path from sys.traces where is_default = 1 ; set @curr_tracefilename = reverse(@curr_tracefilename)

select @indx  = PATINDEX(‘%\%’, @curr_tracefilename); set @curr_tracefilename = reverse(@curr_tracefilename)

set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) – @indx) + ‘\log.trc’;

 

insert into @temp_trace select ObjectName,DatabaseName,StartTime,EventClass,EventSubClass,ObjectType,ServerName,LoginName,ApplicationName,’temp’

from ::fn_trace_gettable( @base_tracefilename, default ) where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2 

———————————————————————————————————————————-

and DatabaseName = ‘SomeDB’ — <<<======Specify the name of the database here, else comment this line to get details of databases

———————————————————————————————————————————-

update @temp_trace set ddl_operation = ‘CREATE’ where event_class = 46

update @temp_trace set ddl_operation = ‘DROP’ where event_class = 47

update @temp_trace set ddl_operation = ‘ALTER’ where event_class = 164

 

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

 

select  start_time as Event_Time,Database_name,Server_name,Login_name,Application_name,

–SQLInstance,

              DDL_Operation from @temp_trace where object_type not in (21587)

order by start_time  desc

 

end