Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
Last active July 6, 2017 21:18
Show Gist options
  • Save lionofdezert/4380717 to your computer and use it in GitHub Desktop.
Save lionofdezert/4380717 to your computer and use it in GitHub Desktop.
To create DDL Change Log using Service Broker, for multiple databases on a single instance
/*
Script By: Aasim Abdullah/Amna Asif for ConnectSQL
Purpose: To create DDL Change Log using Service Broker,
for multiple databases on a single instance
*/
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'ConnectDBA')
CREATE DATABASE ConnectDBA
GO
USE [ConnectDBA]
GO
/*Create a table to hold change log*/
CREATE TABLE [dbo].DDLChangeLog(
[EventType] [varchar](250) NULL,
[PostTime] [datetime] NULL,
[ServerName] [varchar](250) NULL,
[LoginName] [varchar](250) NULL,
[UserName] [varchar](250) NULL,
[DatabaseName] [varchar](250) NULL,
[SchemaName] [varchar](250) NULL,
[ObjectName] [varchar](250) NULL,
[ObjectType] [varchar](250) NULL,
[TSQLCommand] [varchar](max) NULL
) ON [PRIMARY]
GO
--Enable Service Broker
IF EXISTS ( SELECT *
FROM sys.databases
WHERE name = 'ConnectDBA'
AND is_broker_enabled = 0 )
ALTER DATABASE ConnectDBA SET ENABLE_BROKER ;
GO
--Create a stored procedure which will hold logic, how to get data from queue and insert to DDLChangeLog table.
CREATE PROCEDURE [ConnectDBA_Queue_EventNotificatier]
WITH EXECUTE AS OWNER
AS
DECLARE @message_body XML
WHILE ( 1 = 1 )
BEGIN
BEGIN TRANSACTION
-- Receive the next available message FROM the queue
WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time
@message_body = CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))
FROM dbo.[ConnectDBA_EventNotificationQueue] ), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
IF ( @@ROWCOUNT = 0 )
BEGIN
ROLLBACK TRANSACTION
BREAK
END
INSERT INTO DDLChangeLog
SELECT @message_body.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(128)') AS EventType,
CONVERT(DATETIME, @message_body.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)'))
AS PostTime,
@message_body.value('(/EVENT_INSTANCE/ServerName)[1]',
'varchar(128)') AS ServerName,
@message_body.value('(/EVENT_INSTANCE/LoginName)[1]',
'varchar(128)') AS LoginName,
@message_body.value('(/EVENT_INSTANCE/UserName)[1]',
'varchar(128)') AS UserName,
@message_body.value('(/EVENT_INSTANCE/DatabaseName)[1]',
'varchar(128)') AS DatabaseName,
@message_body.value('(/EVENT_INSTANCE/SchemaName)[1]',
'varchar(128)') AS SchemaName,
@message_body.value('(/EVENT_INSTANCE/ObjectName)[1]',
'varchar(128)') AS ObjectName,
@message_body.value('(/EVENT_INSTANCE/ObjectType)[1]',
'varchar(128)') AS ObjectType,
@message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)') AS TSQLCommand
COMMIT TRANSACTION
END
GO
--Create Queue To Catch Messages
CREATE QUEUE [ConnectDBA_EventNotificationQueue]
WITH ACTIVATION -- Setup Activation Procedure
( STATUS= ON, PROCEDURE_NAME = DBO.[ConnectDBA_Queue_EventNotificatier],-- Procedure to execute
MAX_QUEUE_READERS = 2, -- maximum concurrent executions of the procedure
EXECUTE AS OWNER) -- account to execute procedure under
GO
--Create Service
CREATE SERVICE [ConnectDBA_EventNotificationService]
AUTHORIZATION dbo ON QUEUE dbo.ConnectDBA_EventNotificationQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
/*==========ON TARGET DATABASE===================*/
-- Enable service broker on target database
IF EXISTS ( SELECT *
FROM sys.databases
WHERE name = 'NotifierChekcerDB'
AND is_broker_enabled = 0 )
ALTER DATABASE YourDBNameHere SET ENABLE_BROKER ;
GO
--
USE YourDBNameHere
GO
--Create event notification
CREATE EVENT NOTIFICATION [ConnectDBA_NotifierFor_DDL_DATABASE_LEVEL_EVENTS] ON
DATABASE FOR --DDL_DATABASE_LEVEL_EVENTS, -- uncomment to get all type of events information
CREATE_TABLE,ALTER_TABLE,DROP_TABLE,CREATE_VIEW,ALTER_VIEW,DROP_VIEW,CREATE_INDEX,
ALTER_INDEX,DROP_INDEX,CREATE_FUNCTION,ALTER_FUNCTION,DROP_FUNCTION,CREATE_PROCEDURE,
ALTER_PROCEDURE,DROP_PROCEDURE,CREATE_TRIGGER,ALTER_TRIGGER,DROP_TRIGGER,CREATE_SCHEMA,
ALTER_SCHEMA,DROP_SCHEMA
TO SERVICE 'ConnectDBA_EventNotificationService'
, '709CD726-2F34-4B67-9AAF-05D0DB31D5A8' ---- service_broker_guid of ConnectDBA DB
-- SELECT service_broker_guid FROM SYS.DATABASES WHERE name = 'ConnectDBA'
GO
----------How to get DDL Change Log information--------
--USE ConnectDBA
--GO
--SELECT * FROM DBO.DDLChangeLog
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment