USE pubs
GO

--first we need a queue
CREATE QUEUE evtDdlNotif
WITH STATUS = ON

--then we can create the service
CREATE SERVICE evtDdlService
ON QUEUE evtDdlNotif
--this is a built-in contract
--which uses an existing message type
-- http://schemas.microsoft.com/SQL/Notifications/EventNotification 
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE EVENT NOTIFICATION ddlEvents
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
TO SERVICE 'evtDdlService', 'current database'
GO

-- cause it to fire
CREATE TABLE sometable (id INT)
GO

-- check the queue
SELECT * FROM evtDdlNotif
GO

DECLARE @msgtypeid INT
DECLARE @msg XML

WAITFOR(
RECEIVE TOP(1)
@msgtypeid = message_type_id,
@msg = CONVERT(XML, message_body)
FROM evtDdlNotif
)
--check if this is the correct message type
IF @msgtypeid = 4
BEGIN
--do something useful WITH the message
--here we just select it as a result
SELECT @msg
END
