use DBATools

CREATE TABLE [dbo].[Logging](
	[LoggingID] [int] IDENTITY(1,1) NOT NULL,
	[EventTime] [datetime] not NULL,
	[EventType] [nvarchar](100) NULL,
	[LoginName] [nvarchar](50)  NULL,
	[UserName] [nvarchar](100)  NULL,
	[HostName] [nvarchar](50)  NULL,
	[DatabaseName] [nvarchar](50)  NULL,
	[SchemaName] [nvarchar](100)  NULL,
	[ObjectName] [nvarchar](100)  NULL,
	[ObjectType] [nvarchar](100)  NULL,
	[FullLog] [xml] NULL,
	[MessageType] [nvarchar](256)  NULL
) ON [PRIMARY]

alter table [dbo].[Logging] add constraint pk_logging primary key ([EventTime],[LoggingID])

CREATE PROCEDURE [dbo].[LoggingProc]
AS
SET NOCOUNT ON;

DECLARE     @message_body XML,
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;

--Endless loop
WHILE (1 = 1)
BEGIN
   -- BEGIN TRANSACTION ;

    -- Receive the next available message

    WAITFOR (
        RECEIVE TOP(1) 
            @message_type_name=message_type_name,
            @message_body=message_body,  
            @dialog = conversation_handle  
            FROM LoggingQueue
    ), TIMEOUT 2000

	--Rollback and exit if no messages were found
	IF (@@ROWCOUNT = 0)
        BEGIN
            --ROLLBACK TRANSACTION ;
            BREAK ;
        END ;

   --End conversation of end dialog message

    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
        END CONVERSATION @dialog ;
    END ;
    ELSE
    BEGIN

	BEGIN TRY
			if @message_body.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)') <> 'CREATE_STATISTICS'
			begin

			INSERT INTO Logging (
			EventTime,
			EventType,
			LoginName,
			UserName,
			HostName,
			DatabaseName,
			SchemaName,
			ObjectName,
			ObjectType,
			FullLog,
			MessageType)
			VALUES
			(
			
			@message_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime'),
			@message_body.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
			@message_body.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'),
			@message_body.value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(100)'),
			@message_body.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(50)'),
			@message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'),
			@message_body.value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(100)'),
			@message_body.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)'),
			@message_body.value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(100)'),
			@message_body,
			@message_type_name)
			end
	END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber;
		raiserror('Error thrown from LoggingProc',16,1) with LOG
	END CATCH;

END 
--COMMIT TRANSACTION 
END 
go

ALTER DATABASE DBATools SET  ENABLE_BROKER 
GO

CREATE QUEUE [dbo].[LoggingQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION (  STATUS = ON , PROCEDURE_NAME = [dbo].[LoggingProc] , MAX_QUEUE_READERS = 2 , EXECUTE AS OWNER  ) ON [PRIMARY] 
go

CREATE SERVICE [LoggingService]  AUTHORIZATION [dbo]  ON QUEUE [dbo].[LoggingQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
go

CREATE ROUTE [LoggingRoute]   AUTHORIZATION [dbo]   WITH  SERVICE_NAME  = N'LoggingService' ,  ADDRESS  = N'LOCAL' 
go


CREATE EVENT NOTIFICATION Logging_Event_Notification 
ON SERVER
FOR DDL_EVENTS
TO SERVICE 'LoggingService', 'current database'
GO