Yesterday, I posted about SQL Server – Management Studio – Schema Changes History Report and Default Trace and their limitations. It does not capture the details of modification. i.e. When a new column is added to a table, you can find out when the changes to the Table were made using Default Trace, but it does not tell which column was added, or what command was used to add.

This information can be captured using DDL Triggers, it is a special kind of trigger which fires in response to DDL statements. A list of events that can be used to fire a DDL Trigger is available on BOL @ DDL Events for Use with DDL Triggers.

DDL Triggers can be of two scopes.

1. Server Level Trigger – By creating Trigger on Server, you can capture events for all Databases on the Server,
2. Database Level Trigger – This scope is limited to a single Database on which the Trigger is created.

DDL Trigger can capture the information about the EVENT that fired it by using EVENTDATA() function. EVENTDATA() returns an XML. For example below XML is returned when a DDL statement for CREATE TABLE is executed:

–- © 2011 – Vishal (http://SqlAndMe.com)
 
USE SqlAndMe
 
CREATE TABLE dbo.SalesYTD
(
CalendarYear  VARCHAR(7),
SalesYTD      DECIMAL(10,2)
)

EVENTDATA() XML:

<EVENT_INSTANCE>
       <EventType>CREATE_TABLE</EventType>
       <PostTime>2011-07-15T20:10:10.733</PostTime>
       <SPID>51</SPID>
       <ServerName>VGAJJAR</ServerName>
       <LoginName>sa</LoginName>
       <UserName>dbo</UserName>
       <DatabaseName>SqlAndMe</DatabaseName>
       <SchemaName>dbo</SchemaName>
       <ObjectName>SalesYTD</ObjectName>
       <ObjectType>TABLE</ObjectType>
       <TSQLCommand>
              <SetOptions   ANSI_NULLS=“ON“ ANSI_NULL_DEFAULT=“ON“ ANSI_PADDING=“ON“
                            QUOTED_IDENTIFIER=“ON“ ENCRYPTED=“FALSE“ />
              <CommandText>CREATE TABLE dbo.SalesYTD
              (
CalendarYear  VARCHAR(7),
                     SalesYTD      DECIMAL(10,2)
              )</CommandText>
       </TSQLCommand>
</EVENT_INSTANCE>

We can capture this information to a table for Auditing using a DDL Trigger.

First of all, we need to create a table to store the information that will be extracted from EVENTDATA XML. It can be created as below:

-– Audit_Log will be used to capture DDL Activities
CREATE TABLE Audit_Log
(
       EventTime            DATETIME,
       LoginName            VARCHAR(50),
       UserName             VARCHAR(50),
       DatabaseName         VARCHAR(50),
       SchemaName           VARCHAR(50),
       ObjectName           VARCHAR(50),
       ObjectType           VARCHAR(50),
       DDLCommand           VARCHAR(MAX)
)

Result:

Command(s) completed successfully.

Now, we can create the actual Trigger to capture DDL Events, I have captured only CREATE / ALTER and DROP events for DDL, for a complete list you can refer the link above:

–- © 2011 – Vishal (http://SqlAndMe.com)
 
USE SqlAndMe
 
CREATE TRIGGER Log_Table_DDL
ON DATABASE
–ON ALL SERVER – by creating it on Server level,
–you can capture below events for all databases on server
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
–Fires only for CREATE / ALTER / DROP Table
AS
DECLARE       @eventInfo XML
SET           @eventInfo = EVENTDATA()
 
INSERT INTO Audit_Log VALUES
(
       REPLACE(CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
       CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')),
       CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/UserName)')),
       CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')),
       CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')),
       CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')),
       CONVERT(VARCHAR(50),
              @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')),
       CONVERT(VARCHAR(MAX),
              @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

Result:

Command(s) completed successfully.

Once the Trigger is created, it will capture events in Audit_Log. Now let’s execute some DDL which can be captured:

USE SqlAndMe
 
CREATE TABLE MyTable
(
       ID_Number     INT,
)
 
ALTER TABLE MyTable
ADD Name VARCHAR(50)
 
DROP TABLE MyTable
 
CREATE VIEW v_Audit_Log AS
SELECT *
FROM   Audit_Log

Please note that the last statement creates a new VIEW, which will not be captured by Log_Table_DDL. Now let’s look at Audit_Log and see what has been captured:

SELECT *
FROM   Audit_Log

Result Set:

Hope This Helps! Cheers!