Navi's

Necessity, the mother of invention

  • Visitor Map

    Locations of Site Visitors
  • Flag Counter

    free counters

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”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: