如何查看数据库变更记录

如何查看数据库变更记录第⼀步：建库建表
SQL code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29CREATE DATABASE AuditDB
GO
USE [AuditDB]
GO
IF EXISTS (SELECT* FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__DDLEvents__Event__7E6CC920]') AND type = 'D')
BEGIN
ALTER TABLE[dbo].[DDLEvents] DROP CONSTRAINT[DF__DDLEvents__Event__7E6CC920]
END
GO
USE [AuditDB]
GO
/****** Object: Table[dbo].[DDLEvents] Script Date: 10/29/2012 17:29:26 ******/
IF EXISTS (SELECT* FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DDLEvents]') AND type in(N'U'))
DROP TABLE[dbo].[DDLEvents]
GO
USE [AuditDB]
GO
/****** Object: Table[dbo].[DDLEvents] Script Date: 10/29/2012 17:29:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE[dbo].[DDLEvents](
[EventDate] [datetime] NOT NULL,
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](255) NULL,
[SchemaName] [nvarchar](255) NULL,
[ObjectName] [nvarchar](255) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON[PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE[dbo].[DDLEvents] ADD DEFAULT(getdate()) FOR[EventDate]
GO
⼆步：在需要监控的库上执⾏这个脚本，对DDL操作会记录在第⼀步中的库中
SQL code
1
2
3
4
5
6
7
8
9
10
11
IF EXISTS (SELECT* FROM sys.triggers WHERE parent_class_desc = 'DATABASE'AND name= N'DDLTriggertTrace')
DISABLE TRIGGER[DDLTriggertTrace] ON DATABASE
GO
/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 10/29/2012 17:29:35 ******/
IF EXISTS (SELECT* FROM sys.triggers WHERE parent_class_desc = 'DATABASE'AND name= N'DDLTriggertTrace')DROP TRIGGER[DDLTriggertTrace] ON DATABASE
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 5511
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
ON DATABASE
GO
/****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 10/29/2012 17:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER[DDLTriggertTrace] ON DATABASE
--捕获存储过程、视图、表的创建、修改、删除动作
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_VIEW,
ALTER_VIEW, DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
SET NOCOUNT ON;
DECLARE@EventData XML = EVENTDATA() ;--返回有关服务器或数据库事件的信息，以XML格式保存。

DECLARE@ip VARCHAR(32) = ( SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
) ;
INSERT AuditDB.dbo.DDLEvents
( EventType ,
EventDDL ,
EventXML ,
DatabaseName ,
SchemaName ,
ObjectName ,
HostName ,
IPAddress ,
ProgramName ,
LoginName
)
SELECT@EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'NVARCHAR(100)') ,
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]',
'NVARCHAR(MAX)') ,
@EventData ,
DB_NAME() ,
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',
'NVARCHAR(255)') ,
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',
'NVARCHAR(255)') ,
HOST_NAME() ,
@ip ,
PROGRAM_NAME() ,
SUSER_SNAME() ;
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
GO。