Script to find last DDL changes in SQL Server

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a script to find who last performed the DDL changes in SQL Server.

As a DBA, this script is handy to monitor recent DDL changes in SQL Server.

Below is a script:

SELECT

te.name AS eventtype

,t.loginname

,t.spid

,t.starttime

,t.objectname

,t.databasename

,t.hostname

,t.ntusername

,t.ntdomainname

,t.clientprocessid

,t.applicationname

FROM sys.fn_trace_gettable

(

CONVERT

(VARCHAR(150)

SELECT TOP 1

value

FROM sys.fn_trace_getinfo(NULL)

WHERE property = 2

)),DEFAULT

) T

INNER JOIN sys.trace_events as te

ON t.eventclass = te.trace_event_id

WHERE eventclass=164

In the above script, sys.fn_trace_getinfo(NULL) will fetch default trace file.

Jun 28, 2015Anvesh Patel

Comments: 3

kranthi

March 25, 2016 at 4:48 am

Hi Anvesh,

I am getting below error when i try to execute the query.
Msg 19050, Level 16, State 1, Line 1
Trace file name ” is invalid.

Anvesh Patel
March 25, 2016 at 6:24 am
Yes, this is may be problem with your trace file name.
Please change the trace file name or try to generate new trace file.
Anvesh Patel
March 25, 2016 at 6:24 am
Yes, this is may be problem with your trace file name.
Please change the trace file name or try to generate new trace file.