sqlserver2005中如何查看数据库的变更记录
即数据库有诸如修改了表字段、增加了表等等，这些操作有没有记录，如何查看
应该可以两个库之间对比。有这样的脚本的。
第一步：建库建表
SQL code? 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') BEGINALTER 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 ONGO SET QUOTED_IDENTIFIER ONGO SET ANSI_PADDING ONGO 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 OFFGO ALTER TABLE [dbo].[DDLEvents] ADD DEFAULT (getdate()) FOR [EventDate] GO
第二步：在需要监控的库上执行这个脚本，对DDL操作会记录在第一步中的库中
SQL code? 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 DATABASEGO /****** Object: DdlTrigger [DDLTriggertTrace] Script Date: 10/29/2012 17:29:35 ******/ SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO 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 OFFGO SET QUOTED_IDENTIFIER OFFGO GO