SQL Server 2005 Track & Notify DDL Events

A quick way to keep track of DDL events on any database:

1. Create a table to log the events:

CREATE TABLE [dbo].[DDLChangeLog]
  (
    [DDLChangeLog_ID] [int] IDENTITY(1, 1) NOT NULL,
    [InsertionDate] [datetime] NOT NULL CONSTRAINT [DF_ddl_log_InsertionDate] DEFAULT ( GETDATE() ),
    [CurrentUser] [nvarchar](50) NOT NULL CONSTRAINT [DF_ddl_log_CurrentUser] DEFAULT ( CONVERT([nvarchar](50), USER_NAME(), ( 0 )) ),
    [LoginName] [nvarchar](50) NOT NULL CONSTRAINT [DF_DDLChangeLog_LoginName] DEFAULT ( CONVERT([nvarchar](50), SUSER_SNAME(), ( 0 )) ),
    [Username] [nvarchar](50) NOT NULL CONSTRAINT [DF_DDLChangeLog_Username] DEFAULT ( CONVERT([nvarchar](50), original_login(),(0)) ),
    [EventType] [nvarchar](100) NULL,
    [objectName] [nvarchar](100) NULL,
    [objectType] [nvarchar](100) NULL,
    [tsql] [nvarchar](MAX) NULL
  )
ON  [PRIMARY]

2. Create a database level trigger to capture the events:

IF EXISTS (SELECT * FROM sys.triggers WHERE name = N'trgLogDDLEvent' AND parent_class=0)
    DROP TRIGGER [trgLogDDLEvent] ON DATABASE

GO
CREATE TRIGGER trgLogDDLEvent ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS
BEGIN

    --Enable ARITHABORT to avoid exceptions
    SET ARITHABORT ON

    --Declarations
    DECLARE @data XML;
    DECLARE @username NVARCHAR(MAX);
    DECLARE @db NVARCHAR(MAX);
    DECLARE @server NVARCHAR(MAX);
    DECLARE @html NVARCHAR(MAX);
    DECLARE @emailsubject NVARCHAR(2048);

    --Capture the database event data
    SET @data = EVENTDATA();
    SET @username = @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)');
    SET @db = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)');
    SET @server = @data.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)');

    --Log the event
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(max)') <> 'CREATE_STATISTICS' 
    BEGIN 

        INSERT  INTO DDLChangeLog (
               EventType, 
               ObjectName, 
               ObjectType, 
               tsql 
        ) 
        VALUES ( 
               @data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'), 
               @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'), 
               @data.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)'), 
               @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(max)') 
        )

        --Format the SQL as pretty HTML
        SELECT TOP 1 @html = COALESCE(@html, ' <style type="text/css"> 
                              <!-- 
                              #changes { 
                                             border: 1px solid silver; 
                                             font-family: Arial, Helvetica, sans-serif; 
                                             font-size: 11px; 
                                             padding: 10px 10px 10px 10px; 
                              } 
                              #changes td.date { 
                                             font-style: italic; 
                              } 
                              #changes td.tsql { 
                                             border-bottom: 1px solid silver; color: #00008B;            
                              } 
                              -->     
                              </style>
                              <table id="changes"> 
                              ') + '<tr class="recordtop"> 
                                             <td class="date">' + CONVERT(CHAR(18), InsertionDate, 113) + '</td> 
                                             <td class="currentuser">' + currentUser + '</td> 
                                             <td class="loginname">' + LoginName + CASE WHEN loginName <> UserName THEN '(' + UserName + ')' ELSE '' END + '</td> 
                                             <td class="eventtype">' + EventType + '</td> 
                                             <td class="objectname">' + ObjectName + ' (' + objectType + ')' + '</td>
                              </tr> 
                              <tr class="recordbase"><td colspan="6" class="tsql"><pre>' + tsql + '</pre></td></tr> 
                              ' 
        FROM           DDLChangeLog 
        ORDER BY       insertionDate DESC; 
        SELECT         @html + ' </table>';
        
        --Notify via email
        SELECT @emailsubject = 'DDL Statement on ' + @server + ': ' +  + @db;

        EXEC msdb.dbo.sp_send_dbmail 
               @recipients ='myemail@gmail.com', 
               @body = @html,  
               @subject = @emailsubject, 
               @profile_name = 'SQLMail',
               @importance = 'high',
               @body_format = 'HTML';
    END
END