How do you get notified of schema changes in the Sql Server database by email?

Question

We deploy multiple instances of multiple databases to multiple sites. These sites have their own database administrators. Here, at the head office, we would like to track all satellite databases and receive a notification when any of the schemes changes (table structures, saved procs, views, etc.).

Is there any tool that can perform this function?

Answer 1

In SQL Server 2005, you can create DDL triggers in a database that run when a schema is modified. You can then use the database mail to send email.

See: Using DDL Triggers in SQL Server 2005 to Change Schema Schemas

Here is an example that uses EVENTDATA () and the logs in the table:

USE AdventureWorks GO CREATE TABLE [dbo].[tblMonitorChange] ( [EventType] [varchar](100) NULL, [SchemaName] [varchar](100) NULL, [ObjectName] [varchar](100) NULL, [ObjectType] [varchar](100) NULL, [EventDate] [datetime] NULL, [SystemUser] [varchar](100) NULL, [CurrentUser] [varchar](100) NULL, [OriginalUser] [varchar](100) NULL ) USE AdventureWorks GO CREATE TRIGGER trgMonitorChange ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS set nocount on declare @EventType varchar(100) declare @SchemaName varchar(100) declare @ObjectName varchar(100) declare @ObjectType varchar(100) SELECT @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') ,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)') ,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') ,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') -- Is the default schema used if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p on u.uid = p.principal_id where u.name = CURRENT_USER insert into tblMonitorChange select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN() 

From here .