DDL Audit logging

/* DDL AUDIT LOG TABLE
   Docs:
   - DDL triggers: https://learn.microsoft.com/sql/relational-databases/triggers/ddl-triggers
   - EVENTDATA(): https://learn.microsoft.com/sql/t-sql/functions/eventdata-transact-sql
   - DDL event groups: https://learn.microsoft.com/sql/relational-databases/triggers/ddl-events
   - ORIGINAL_LOGIN(), APP_NAME(), HOST_NAME(): https://learn.microsoft.com/sql/t-sql/functions/original-login-transact-sql
*/
IF OBJECT_ID(N'dbo.DDL_AuditLog', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.DDL_AuditLog
    (
        LogId              bigint IDENTITY(1,1) PRIMARY KEY,
        EventType          nvarchar(200)  NULL,
        PostTime           datetime2(3)   NULL,
        ServerName         sysname        NULL,
        DatabaseName       sysname        NULL,
        SchemaName         sysname        NULL,
        ObjectName         sysname        NULL,
        ObjectType         nvarchar(200)  NULL,
        TSQLCommand        nvarchar(max)  NULL,
        LoginName          sysname        NULL,
        UserName           sysname        NULL,
        OriginalLogin      sysname        NULL,
        ApplicationName    nvarchar(256)  NULL,
        HostName           nvarchar(256)  NULL,
        SessionId          int            NULL, -- @@SPID at capture time
        EventXml           xml            NOT NULL,
        InsertedAtUtc      datetime2(3)   NOT NULL CONSTRAINT DF_DDL_AuditLog_InsertedAtUtc DEFAULT (SYSUTCDATETIME())
    );
END;
GO

/* DATABASE-SCOPE DDL TRIGGER
   Docs:
   - DDL triggers: https://learn.microsoft.com/sql/relational-databases/triggers/ddl-triggers
   - EVENTDATA() schema: https://learn.microsoft.com/sql/t-sql/functions/eventdata-transact-sql
   - DDL event groups: https://learn.microsoft.com/sql/relational-databases/triggers/ddl-events
*/
IF OBJECT_ID(N'trg_DDL_Audit', N'TR') IS NOT NULL
    DROP TRIGGER trg_DDL_Audit ON DATABASE;
GO

CREATE TRIGGER dbo.trg_DDL_Audit
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS  -- captures CREATE/ALTER/DROP and more at DB scope
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x xml = EVENTDATA();

    BEGIN TRY
        INSERT dbo.DDL_AuditLog
        (
            EventType,
            PostTime,
            ServerName,
            DatabaseName,
            SchemaName,
            ObjectName,
            ObjectType,
            TSQLCommand,
            LoginName,
            UserName,
            OriginalLogin,
            ApplicationName,
            HostName,
            SessionId,
            EventXml
        )
        SELECT
            @x.value('(EVENT_INSTANCE/EventType)[1]',       'nvarchar(200)'),
            @x.value('(EVENT_INSTANCE/PostTime)[1]',        'datetime2'),
            @x.value('(EVENT_INSTANCE/ServerName)[1]',      'sysname'),
            @x.value('(EVENT_INSTANCE/DatabaseName)[1]',    'sysname'),
            @x.value('(EVENT_INSTANCE/SchemaName)[1]',      'sysname'),
            @x.value('(EVENT_INSTANCE/ObjectName)[1]',      'sysname'),
            @x.value('(EVENT_INSTANCE/ObjectType)[1]',      'nvarchar(200)'),
            @x.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'),
            @x.value('(EVENT_INSTANCE/LoginName)[1]',       'sysname'),
            @x.value('(EVENT_INSTANCE/UserName)[1]',        'sysname'),
            ORIGINAL_LOGIN(),
            APP_NAME(),
            HOST_NAME(),
            @@SPID,
            @x;
    END TRY
    BEGIN CATCH
        -- best-effort: swallow to avoid breaking the DDL statement
        -- optional: write minimal info if needed
    END CATCH
END;
GO