USE tempdb
GO

--first create a table to log to
CREATE TABLE dbo.ddlLog (id INT PRIMARY KEY IDENTITY, 
    logTxt XML)
GO

--create our test table
CREATE TABLE dbo.triTest (id INT PRIMARY KEY)
GO

-- create the trigger
CREATE TRIGGER ddlTri
ON DATABASE
AFTER DROP_TABLE
AS
INSERT INTO dbo.ddlLog VALUES('table dropped')
GO

-- use it 
DROP TABLE triTest
GO
-- check the trigger output
SELECT * FROM dbo.ddlLog
GO

-- Seeing the eventdata()

-- alter the trigger
ALTER TRIGGER ddlTri
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO ddlLog VALUES(eventdata())
GO

-- try it out
-- delete all entries in ddlLog
DELETE ddlLog
GO

--create a new table
CREATE TABLE evtTest (id INT PRIMARY KEY)
GO

--select the logTxt column with the XML
SELECT logTxt FROM ddlLog
GO

-- post process eventdata()
-- check ddlLog
SELECT id FROM ddlLog

-- use an id value that's in there
DECLARE @data XML
SELECT @data = logTxt FROM ddlLog
  WHERE id = 3

SELECT
@data.value('(EVENT_INSTANCE/EventType)[1]','NVARCHAR(50)') AS 'EventType',
@data.value('(EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(128)') AS 'Object',
@data.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(4000)') 
   AS 'CommandText'
GO

-- You should see the ALTER TRIGGER eventdata()
-- after running this!
ALTER TRIGGER ddlTri
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS 
DECLARE @x XML
SET @x = Eventdata()
SELECT Tab.Col.value('./EventType[1]','NVARCHAR(50)') AS 'EventType',
 Tab.Col.value('./PostTime[1]','datetime') AS  'PostTime',
 Tab.Col.value('./SPID[1]','NVARCHAR(50)') AS  'SPID',
 Tab.Col.value('./ServerName[1]','NVARCHAR(50)') AS  'ServerName',
 Tab.Col.value('./LoginName[1]','NVARCHAR(50)') AS 'LoginName',
 Tab.Col.value('./UserName[1]','NVARCHAR(50)') AS 'UserName',
 Tab.Col.value('./DatabaseName[1]','NVARCHAR(128)') AS 'DatabaseName',
 Tab.Col.value('./SchemaName[1]','NVARCHAR(128)') AS 'SchemaName',
 Tab.Col.value('./ObjectName[1]','NVARCHAR(128)') AS 'ObjectName',
 Tab.Col.value('./ObjectType[1]','NVARCHAR(50)') AS 'ObjectType',
 Tab.Col.value('./TSQLCommand[1]/CommandText[1]','NVARCHAR(4000)') 
   AS 'CommandText',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULLS',
   'NVARCHAR(3)') AS 'ANSI_NULLS_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_NULL_DEFAULT',
   'NVARCHAR(3)') AS 'ANSI_NULL_DEFAULT_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ANSI_PADDING',
   'NVARCHAR(3)') AS 'ANSI_PADDING_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@QUOTED_IDENTIFIER',
   'NVARCHAR(3)') AS 'QUOTED_IDENTIFIER_OPTION',
 Tab.Col.value('./TSQLCommand[1]/SetOptions[1]/@ENCRYPTED_OPTION',
   'NVARCHAR(4)') AS 'ENCRYPTED_OPTION'
FROM @x.nodes('/EVENT_INSTANCE') AS Tab(Col)
GO

-- drop the trigger
DROP TRIGGER ddlTri ON DATABASE
GO
