How to audit Database Design Modifications using a DDL Trigger

September 27, 2012 · Posted in SQL, Technology 

Recently we identified the need to create a system for tracking changes made to our databases by developers and internal systems. Although the database itself is using FULL logging, which allows you to view changes made, we found that we’d like to see all modifications made over the entire history of the database in question. This prompted me to develop a table containing all changes that could be easily queried. The result of that work is below.

CREATE TABLE dbo.DesignLog(
    DateStamp datetime NULL CONSTRAINT DF_DesignLog_DateStamp DEFAULT (getdate()),
    HostName nvarchar(255) NULL CONSTRAINT DF_DesignLog_HostName DEFAULT (host_name()),
    UserName nvarchar(255) NULL CONSTRAINT DF_DesignLog_UserName DEFAULT (suser_sname()),
    ActionType nvarchar(255) NULL,
    StatementExecuted nvarchar(max) NULL
CREATE TRIGGER [DesignLogTrigger]
        Authored By: Max Vernon
        Author Date:    2012-07-05
        Purpose:        Capture DDL Statements executed against database

    DECLARE @data XML;
    DECLARE @ActionType nvarchar(max);
    DECLARE @Statement nvarchar(max);
    DECLARE @msg nvarchar(max);
    SET @data = EVENTDATA();
    SET @msg = cast(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)') as nvarchar(max));
    SET @msg = dbo.xmltostring(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)'));
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @ActionType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)');
    SET @Statement = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
    IF @msg NOT LIKE 'UPDATE STATISTICS' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REORGANIZE' + char(37) + ''
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REBUILD' + char(37) + ''
        BEGIN TRAN;
        INSERT INTO DesignLog
            , StatementExecuted
            , @Statement
        COMMIT TRAN;

This DDL Trigger does not log certain events such as UPDATE STATISTICS, INDEX REBUILD, AND INDEX REORGANIZE. We are not interested in logging these events since these events happen during our nightly database maintenance tasks, and therefore crowd out the meaningful changes made by developers.

The advantage of this particular trigger is that it captures the actual SQL text of the event, thereby creating a log of changes made to all objects in the database. This allows you to revert back to a prior version of any given object – a sort of manual revision control system.

The XMLUnescaper.XMLtoString function source code is written in VB.Net and uploaded to the SQL Server as a CLR function. That source code is:

Option Explicit On

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
    Public Shared Function XMLToString(ByVal XML As System.Data.SqlTypes.SqlXml) As System.Data.SqlTypes.SqlString

        Dim sXML As String = XML.Value
        Dim sTemp1 As String
        Dim sTemp2 As String
        Dim sTemp3 As String
        Dim a As Int32
        Dim b As Int32
        Dim c As Int32
        Dim s As String

            sXML = Replace(sXML, ">", ">")
            sXML = Replace(sXML, "<", "<")             sXML = Replace(sXML, "&", "&")             sXML = Replace(sXML, """, Chr(34))
            For a = 1 To Len(sXML)
                b = InStr(a, sXML, "&#x")
                If b > 0 Then
                    c = InStr(b, sXML, ";")
                    s = Mid(sXML, b + 3, c - b - 3)
                    sTemp1 = Left(sXML, b - 1)
                    sTemp2 = Chr("&H" & s)
                    sTemp3 = Right(sXML, Len(sXML) - (c + 1))
                    sXML = sTemp1 & sTemp2 & sTemp3
                    Exit For
                End If
        Catch ex As Exception
            sXML = ex.ToString & vbCrLf & vbCrLf & "Len(XML) = " & Len(XML) & vbCrLf & vbCrLf & sXML
        End Try

        Return sXML
    End Function

    Public Shared Function RemoveTag( ByVal XML As String, ByVal TagToRemove As String) As System.Data.SqlTypes.SqlString

        Return Replace(XML, TagToRemove, "")

    End Function

End Class

A binary version of XMLUnescaper is available from


2 responses to “How to audit Database Design Modifications using a DDL Trigger”

  1. Abid says:

    Getting This error
    Msg 4121, Level 16, State 1, Procedure DesignLogTrigger, Line 27
    Cannot find either column “dbo” or the user-defined function or aggregate “dbo.xmltostring”, or the name is ambiguous.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please answer the anti-spam question: * Time limit is exhausted. Please reload CAPTCHA.