Getting notified of structure changes via DDL Triggers

This procedure notifies our DBA Group of table and view changes by using a Database trigger. The trigger exposes the system function EVENTDATA() which returns XML data. See EVENTDATA in BOL for schema. This parses the XML to get information needed, and then uses the old standby sp_send_dbmail. Once the trigger is created they can be managed via SSMS by the path Database\Programmability\Database Triggers

USE MyDatabase

GO

CREATE TRIGGER Notify_Table_changes_MyDataBase ON DATABASE

FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE, CREATE_VIEW, ALTER_VIEW,

DROP_VIEW

AS

/*

Can View triggers under Database\Programmability\Database Triggers

*/

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @Recipients VARCHAR(MAX),

@Body VARCHAR(2500),

@Subject VARCHAR(50)

SET @Recipients = 'DBAGroup@MyCo.com'

SET @Subject = @@SERVERNAME +':'+RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/ServerName)[1]',

'nvarchar(100)'))) + '-'

+ RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]',

'nvarchar(100)'))) + '-' + RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')))

+ '-' + RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/ObjectName)[1]',

'nvarchar(100)')))

SET @Body = 'User:' + CONVERT(NVARCHAR(100), SYSTEM_USER) + ' Ran:'

+ @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Dataprofile',

@recipients = @Recipients, @body = @Body, @subject = @Subject