I use below trigger to email me when a database is created or dropped.

  

CREATE  TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
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 'Database has been created'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients = 'abc@xyz',
@subject = 'A new database has been created!',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END

GO

ENABLE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER
GO

is there a way we can get notification when a table is created or altered or dropped ?

Please share if you have script

Thanks

Use a Database Trigger, for example...

CREATE TRIGGER AuditProcChanges
ON DATABASE
FOR CREATE_PROC, ALTER_PROC, DROP_PROC, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS

DECLARE @ed XML
SET @ed = EVENTDATA()

INSERT INTO DDLAudit (PostTime, DatabaseName, Event, ObjectName, TSQL, Login)
VALUES
(
GetDate(),
@ed.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@ed.value('(/EVENT_INSTANCE/EventType)[1]', nvarchar(100)'),
@ed.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@ed.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
@ed.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')

... and if you want to cover all databases without creating one trigger per database, you can use Event Notifications instead (see CREATE EVENT NOTIFICATION).

 CREATE TRIGGER [TableCreated]
ON DATABASE
FOR  CREATE_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 created'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients = 'abc@xyz.com',
@subject = 'A new Table has been created!',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END




GO

ENABLE TRIGGER [TableCreated] ON DATABASE
GO

the above worked

    

• Marked as answer by Nick1990_K 12 hours 38 minutes ago

 CREATE TRIGGER [TableCreated]
ON DATABASE
FOR  CREATE_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 created'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default',
@recipients = 'abc@xyz.com',
@subject = 'A new Table has been created!',
@body_format = 'HTML',
@importance = 'High',
@body = @bd
END




GO

ENABLE TRIGGER [TableCreated] ON DATABASE
GO

the above worked

    

• Marked as answer by Nick1990_K Friday, July 24, 2015 6:29 PM