Alert when a table is Created or Dropped

Use below trigger to send email when a table is dropped

use the same trigger and change for Create Table and also change the subject in email ..

 

 

CREATE TRIGGER [TableDropped]

ON DATABASE

FOR  DROP_TABLE

 

AS 

DECLARE @bd VARCHAR(MAX)

DECLARE @tsql VARCHAR(MAX)

SET @tsql = EVENTDATA().value

        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(max)')

SET @bd = 'UserName: ' + UPPER(SUSER_NAME()) + '

   ServerName: ' + @@SERVERNAME + '

   Time: ' + CONVERT(VARCHAR(25),GETDATE()) + '

   HostName: ' + HOST_NAME() + '

   Database: ' + DB_NAME() + '

   T-SQL: ' +  @tsql

   

BEGIN

PRINT 'Table has been Dropped'

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

 @recipients = 'abc@xyz.com'

 @subject = 'A new Table has been Dropped!',

 @body_format = 'HTML',

 @importance = 'High',

 @body = @bd

END

GO

DISABLE TRIGGER [TableDropped] ON DATABASE

GO