Audit SQL modifications and get notified in SQL Server

Below script can be used to capture DDL modifications into a table and trigger an email notification for the same.

Script to create a trigger : This will capture even the user and hostname.

USE DBAAdmin
GO

/****** Object:  DDLTrigger [object_change_notification]    Script Date: 5/3/2018 11:54:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [object_change_notification]
ON DATABASE
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE
AS

   Declare @Hostname varchar(20) = HOST_NAME()
   DECLARE @sys_usr char(30) SET @sys_usr = SYSTEM_USER
   Declare @executiontime datetime =getdate()
   DECLARE @data XML = EVENTDATA()
   DECLARE @eventType nvarchar(100)= CONCAT ('EVENT: ',@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),+ CHAR(13))
   DECLARE @TsqlCommand nvarchar(2000)=CONCAT('COMMAND:   ',@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
   DECLARE @BodyMsg nvarchar(2100)=CONCAT(@eventType , @sys_usr, @Hostname ,@executiontime , @TsqlCommand)

   insert into DBAAdmin.dbo.Audit_Object select @sys_usr,@Hostname,@executiontime,@data,@eventType,@TsqlCommand,@BodyMsg
 
GO


Use the below, stored procedure to schedule a job as per needs( I suggest daily once at 9PM). This will update all the modification done in the day.

Use DBAAdmin
go

Create Procedure Audit_Notification
As
DECLARE @Body VARCHAR(8000)
DECLARE @Qry varchar(8000)

if exists (select * from OperationsDB.dbo.Audit_Object where convert(DATE,executiontime)=CONVERT(date,getdate()-1)
Begin
DECLARE @tab char(1) = CHAR(9)
SET @Body ='Hi All,'
SET @Qry='set nocount on;
select * from OperationsDB.dbo.Audit_Object where convert(DATE,executiontime)=CONVERT(date,getdate()-1)
'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'MAIL_Profile',
@recipients = 'adil9025@gmail.com',
--@blind_copy_recipients='mailid',
@body= @Body,
@query=@Qry,
@subject='The following object(s) was/were changed',
@attach_query_result_as_file = 1,
@query_attachment_filename='AuditSheet.xls',
@query_result_separator=@tab,
@query_result_no_padding=1

End

go

         By Mohammed Adil