Modified,Created & Deleted History in SQL

Question

How to finding out who has modified,Created and Deleted a Stored Procedure or table in details on SQL Server?

Sachin Singh · Answer

check below to see who has modified SP and other database object and when DECLARE @filename VARCHAR(255) SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX( '\', REVERSE(path))+1) + ' \Log.trc' FROM sys.traces WHERE is_default = 1; SELECT gt.HostName, gt.ApplicationName, gt.NTUserName, gt.NTDomainName, gt.LoginName, gt.SPID, gt.EventClass, te.Name AS EventName, gt.EventSubClass, gt.TEXTData, gt.StartTime, gt.EndTime, gt.ObjectName, gt.DatabaseName, gt.FileName, gt.IsSystem FROM [fn_trace_gettable](@filename, DEFAULT) gt JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id WHERE EventClass in (164) --AND gt.EventSubClass = 2 ORDER BY StartTime DESC;

Thanh An · Answer

If the SQL Server has not been restarted (and the plan has not been fired, etc.), you can find the query in the plan's cache. SELECT t.[text] FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t WHERE t.[text] LIKE N '%something unique about your query%' ;

Mageshwaran R · Answer

Hi Mohan, https://blog.devart.com/sql-server-query-history.html

Modified,Created & Deleted History in SQL

Answers (3)