如何查看数据库变更记录

第一步：建库建表

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
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
CREATE 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
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
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
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
posted @ 2012-11-10 13:57  attitudedecidesall  Views(1076)  Comments(0)  Edit  收藏  举报