Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 | 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,
