How to stay in sync with others, at your level

== Part Two ! ==

I have since lost the URL that first inspired this code, and when I find it, I'll update here.

Here's the basic idea for the DDL triggers:

Create a logging table:

CREATE TABLE dbo.DDLEvents

(

 EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

 EventType NVARCHAR(64),

 EventDDL NVARCHAR(MAX),

 EventXML XML,

 DatabaseName NVARCHAR(255),

 SchemaName NVARCHAR(255),

 ObjectName NVARCHAR(255),

 [ObjectID] INT,

 HostName VARCHAR(64),

 IPAddress VARCHAR(32),

 ProgramName NVARCHAR(255),

 LoginName NVARCHAR(255)

);

...and then, create a trigger that uses it. In this case, I'm making one that's at the server level. This is going to capture the events ALTER_DATABASE, CREATE_DATABASE, or DROP_DATABASE whenever they happen.

CREATE TRIGGER ddl_trigger
ON ALL SERVER
FOR ALTER_DATABASE, CREATE_DATABASE , DROP_DATABASE
AS
begin
 DECLARE @WhatHappened XML ;

 select @WhatHappened = EVENTDATA() ;

 DECLARE @ip VARCHAR(32) = (
  SELECT client_net_address
  FROM sys.dm_exec_connections
  WHERE session_id = @@SPID
  );
 INSERT AuditSampleDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
     SELECT
        @WhatHappened.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
        @WhatHappened.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @WhatHappened,
        DB_NAME(),
        @WhatHappened.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @WhatHappened.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END

GO