Track Who did DDL Changes . Drop/Alter/Create

USE [DBA]







GO



/****** Object: Table [dbo].[objectlist] Script Date: 11/29/2017 1:35:03 AM ******/


--SET ANSI_NULLS ON


--GO


--SET QUOTED_IDENTIFIER ON


--GO


--IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[objectlist]') AND type in (N'U'))


--BEGIN


--CREATE TABLE [dbo].[objectlist](


-- [name] [sysname] NOT NULL,


-- [objectid] [int] NULL,


-- [dbname] [sysname] NOT NULL,


-- type_desc nvarchar(500)










--) ON [PRIMARY]


--END


--GO










--Truncate table DBA.dbo.objectlist










--EXEC master.sys.sp_MSforeachdb


--'use [?];









　


--Insert DBA.dbo.objectlist


--select name,object_id,db_name(),type_desc as type from sys.objects









　


--'





SELECT distinct









LoginName


,f.ApplicationName

,f.HostName

,f.NTUserName

,f.NTDomainName

,f.spid

,f.starttime

,f.SessionLoginName

, case









when f.EventClass=46 then 'CREATE'


when f.EventClass=47 then 'DROP'


when f.EventClass=164 then 'ALTER'


end as Events_Capture









,f.ObjectName

,f.objectid

, f.DatabaseName

--,d.type_desc


--,f.*


FROM sys.traces t

CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),


CHARINDEX('\', REVERSE(t.path)),

260)


) + N'log.trc', DEFAULT) f

--inner join DBA.dbo.objectlist as D


--on D.dbname=f.DatabaseName and D.name=f.ObjectName and d.objectid=f.ObjectID


WHERE t.is_default = 1

---AND ObjectName = 'FOO'


AND EventClass IN (46, /*Object:Created*/


47, /*Object:Dropped*/


164 /*Object:Altered*/ )

and f.DatabaseName not in ('tempdb')


--and f.DatabaseID='10'