How To Find Deleted Objects in SQL Server

Faruk Erdem

4 years ago

This article contains script to find deleted objects in sql server. It happened to every database administrator. Application developers or your supervisors may ask you to find out who deleted objects in database. Did you know that you can find deleted objects with TSQL even if there is no audit in the database? Yes, this is possible. You can find deleted objects in SQL Server with the help of the following script.

How to Find Deleted Table in SQL Server

Below scripts will help you to find deleted table in sql server, also you can find who dropped stored procedure in sql server. Of course its not limited only table or stored procedure. It applies to all database objects.

DECLARE @path VARCHAR(MAX) SELECT @path=SUBSTRING(path, 1, LEN(path) - CHARINDEX('_', REVERSE(path))) + '.trc' 
FROM sys.traces WHERE is_default = 1 SELECT e.name AS Name, t.DatabaseName ,t.ObjectName as Deleted_Object,
t.LoginName as 'WhoDeleted', t.StartTime as 'Time',t.HostName 'Hostname',t.ServerName 
FROM sys.fn_trace_gettable(@path,0) t INNER JOIN sys.trace_events e ON t.EventClass = e.trace_event_id 
where e.name = 'Object:Deleted' and DatabaseID>4

DECLARE @path VARCHAR(MAX) SELECT @path=SUBSTRING(path, 1, LEN(path) - CHARINDEX('_', REVERSE(path))) + '.trc'

FROM sys.traces WHERE is_default = 1 SELECT e.name AS Name, t.DatabaseName ,t.ObjectName as Deleted_Object,

t.LoginName as 'WhoDeleted', t.StartTime as 'Time',t.HostName 'Hostname',t.ServerName

FROM sys.fn_trace_gettable(@path,0) t INNER JOIN sys.trace_events e ON t.EventClass = e.trace_event_id

where e.name = 'Object:Deleted' and DatabaseID>4

Also, you can use SQL Auditing to monitor everythins in SQL Server. Below articles will help you to create and monitor SQL Server audit.

How To Create SQL Server Audit,

How To Read SQL Server Audit