using trigger how to track all the ddl event changes done in a database | Saving ddl events log in sql server database

hi in this post i will show, using trigger how to track all the dll changes like alter,create,drop etc in a database.

1. Creating a table which we will be using inside the trigger to save the ddl event log generated.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [DDLAudit] (
 [eventtime] [varchar](50) NULL
 ,[EventType] [nvarchar](500) NULL
 ,[ServerName] [nvarchar](500) NULL
 ,[DatabaseName] [varchar](256) NULL
 ,[ObjectType] [varchar](256) NULL
 ,[ObjectName] [varchar](125) NULL
 ,[UserName] [varchar](200) NULL
 ,[CommandText] [varchar](max) NULL
 ,[XmlEvent] [xml] NOT NULL
 ,[modifiedby] [varchar](200) NULL
 ,[ModifiedOn] [datetime] NULL
 ) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

2. Creating trigger which will get executed for every ddl level changes and will save the event log in the DDLAudit Table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [DBAAudit_ALTER_Database] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS

BEGIN
 SET NOCOUNT ON

 DECLARE @ed XML

 SET @ed = EVENTDATA()

 INSERT INTO DDLAudit (
  eventtime
  ,EventType
  ,ServerName
  ,DatabaseName
  ,ObjectType
  ,ObjectName
  ,UserName
  ,CommandText
  ,[XmlEvent]
  ,modifiedby
  ,ModifiedOn
  )
 VALUES (
  convert(VARCHAR(50), getdate(), 109)
  ,@ed.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(500)')
  ,@ed.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(500)')
  ,@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
  ,CONVERT(VARCHAR(125), @ed.query('data(/EVENT_INSTANCE/ObjectType)'))
  ,@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
  ,suser_name()
  ,Cast(@ed.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)') AS NVARCHAR(max))
  ,@ed
  ,host_name()
  ,GetDate()
  )
END

SET NOCOUNT OFF
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [DBAAudit_ALTER_Database] ON DATABASE
GO

3. Result

Now to check alter or drop or create something in the database and then
execute this below select query to check for ddl event changes getting logged in the table.
 select * from [DDLAudit]