DDL Triggers to Audit DDL Events - SQL Server

DDL Triggers are not only to restrict however we can even audit the DDL changes. In regular triggers we have two magic tables called INSERTED, DELETED which helps to track the changes when any event fires. But to track the DDL changes we have a function EVENTDATA() which helps to track all DDL changes.

Below is the example to track all the DDL changes in the current database.

CREATE TABLE DDL_Audit_Log
(
       EventTime            DATETIME,
       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)
ALTER TRIGGER Trg_LogDDLEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML
SET @data= EVENTDATA()
INSERT INTO DDL_Audit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)