Modified,Created & Deleted History in SQL

Piyush Mohan

5y904

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Thanh An5y ago
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%';
Sachin Singh5y ago
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;
Mageshwaran R5y ago
Hi Mohan,
https://blog.devart.com/sql-server-query-history.html