SQL Server监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)

这篇文章主要为大家详细介绍了SQL Server监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

USE msdb;
GO
CREATE TABLE [dbo].[DatabaseLog]
(
 [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
 [PostTime] [datetime] NOT NULL,
 [DatabaseUser] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LoginName] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [ClientHost] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Event] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [Schema] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [Object] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [TSQL] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [XmlEvent] [xml] NOT NULL,
CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
(
 [DatabaseLogID] ASC
 )WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key for DatabaseLog records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseLogID'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The date and time the DDL change occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'PostTime'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The user who implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseUser'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The login which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'LoginName'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The client machine on which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'ClientHost'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The type of DDL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Event'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The schema to which the changed object belongs.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Schema'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The object that was changed by the DDL statment.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Object'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The exact Transact-SQL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'TSQL'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The raw XML data generated by database trigger.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'XmlEvent'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Audit table tracking all DDL changes made to the database. Data is captured by the database trigger ddlDatabaseTriggerLog.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog'
GO
EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key (nonclustered) constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'CONSTRAINT',@level2name=N'PK_DatabaseLog_DatabaseLogID'
GO

例如，我要监控数据库MyAssistant的DDL操作，那么我们首先在“数据库邮件”里面创建一个配置名为“ DataBase_DDL_Event”的配置文件（profile name）,这个就不多讲了，不知道配置的，自己先练练手把，假如我需要让数据库把监控到DDL操作变动相信信息发送到我的邮箱 *****@***.com(用你自己的邮箱替代)，那么只需要修改下面代码的邮箱和profile_name即可。

代码如下:

USE MyAssistant;
GO

CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @data XML;
 DECLARE @schema sysname;
 DECLARE @object sysname;
 DECLARE @eventType sysname;
 DECLARE @tableHTML NVARCHAR(MAX) ;

 SET @data = EVENTDATA();
 SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
 SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
 SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')

 IF @object IS NOT NULL
 PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
 ELSE
 PRINT ' ' + @eventType + ' - ' + @schema;

 IF @eventType IS NULL
 PRINT CONVERT(nvarchar(max), @data);

 INSERT [msdb].[dbo].[DatabaseLog]
 (
 [PostTime],
 [DatabaseUser],
 [LoginName],
 [ClientHost],
 [Event],
 [Schema],
 [Object],
 [TSQL],
 [XmlEvent]
 )
 VALUES
 (
 GETDATE(),
 CONVERT(sysname, CURRENT_USER),
 @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'),
 CONVERT(sysname, HOST_NAME()),
 @eventType,
 CONVERT(sysname, @schema),
 CONVERT(sysname, @object),
 @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
 @data
 );

 SET @tableHTML = 
 N'<H1>DDL Event</H1>' + 
 N'<table border="0">' + 
 N'<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th>' + 
 N'<th>TSQL</th><th></tr>' + 
 CAST(( SELECT
 td = PostTime, '', 
 td = DatabaseUser, '', 
 td = LoginName, '', 
 td = ClientHost, '', 
 td = TSQL, '' 
 FROM msdb.dbo.DatabaseLog 
 WHERE DatabaseLogID =(select max(DatabaseLogID) from msdb.dbo.DatabaseLog) 
 FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;

 EXEC msdb.dbo.sp_send_dbmail 
 @profile_name = 'DataBase_DDL_Event',
 @recipients='***@***.com', 
 @subject = 'DDL Event - DataBase MyAssistant', 
 @body = @tableHTML, 
 @body_format = 'HTML' ;
END; 
GO

接下来我们来测试一下，假如一个用户Test登录数据库，一不小心删除了一个Test的表，如下图一所示，那么我将收到一封邮件，提示我用户Test在那台客户端主机执行了啥DDL操作(如下图二所示)，当然邮件的样式、排版有兴趣的可以去美化一下。

【图片暂缺】

【图片暂缺】

注：关于SQL Server监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)的内容就先介绍到这里，更多相关文章的可以留意