Find out the modified objects in SQL Server
Posted by Navi's on June 8, 2012
Sometime we need to find out who made the change in SQL Server objects if we are using common Login to connect to the SQL server. For that we can use below query which returns the details of Altered, Deleted etc…
DECLARE @object varchar(5000) SET @object ='objectname' SELECT E.name,ObjectName, I.* FROM sys.traces T CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I Join sys.trace_events E On I.eventclass = E.trace_event_id Where T.id = 1 And E.name = 'Object:Altered' and ObjectName like '%'+@object+'%'
Here we need to set the Object name which we need to find out. We can also find out create, delete object using these “Object:Created” and “Object:Deleted” in where clause where we used “Object:Altered”.