How do you get notification of schema changes in your Sql Server database via email?
We deploy multiple instances of multiple databases to multiple sites. These sites all have their own DBAs. Here at head office we would like to monitor all the satellite databases and get a notification when any of the schemas change (table structures, stored procs, views etc).
Is there any tool available that can perform this function?
In SQL Server 2005 onwards, you can create database-wide DDL triggers, which fire when schema modifications are made. You could then use database mail to send email.
Please see: Using DDL Triggers in SQL Server 2005 to Capture Schema Changes
Here's an example which uses EVENTDATA() and logs to a 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.
精彩评论