原理: 通过创建一个记录表和一个触发器相结合实现的
创建表:VERSION_CONTROL_TABLE
CREATE TABLE [dbo].[VERSION_CONTROL_TABLE]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [databasename] [varchar](256) NULL, [eventtype] [varchar](50) NULL, [objectname] [varchar](256) NULL, [objecttype] [varchar](25) NULL, [sqlcommand] [nvarchar](max) NULL, [loginname] [varchar](256) NULL, [hostname] [varchar](256) NULL, [PostTime] [datetime] NULL, [Version] [int] NOT NULL, CONSTRAINT [PK_VERSION_CONTROL_TABLE] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
创建触发器:TRG_VERSION_CONTROL_TABLE
CREATE TRIGGER [TRG_VERSION_CONTROL_TABLE] ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX AS SET NOCOUNT ON DECLARE @CurrentVersion int DECLARE @CurrentID int DECLARE @DatabaseName varchar(256) DECLARE @ObjectName varchar(256) DECLARE @data XML SET @data = EVENTDATA() INSERT INTO dbo.VERSION_CONTROL_TABLE(databasename, eventtype,objectname, objecttype, sqlcommand, loginname,Hostname,PostTime, Version) VALUES( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), -- value is case-sensitive @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'), HOST_NAME(), GETDATE(), 0 ) SET @CurrentID = IDENT_CURRENT('VERSION_CONTROL_TABLE') SELECT @DatabaseName = databasename, @ObjectName = objectname FROM VERSION_CONTROL_TABLE WHERE ID = @CurrentID IF (@DatabaseName IS NOT NULL AND @ObjectName IS NOT NULL) BEGIN SELECT @CurrentVersion = MAX(Version) FROM VERSION_CONTROL_TABLE WHERE databasename = @DatabaseName AND objectname = @ObjectName UPDATE VERSION_CONTROL_TABLE SET Version = ISNULL(@CurrentVersion, 0) + 1 WHERE ID = @CurrentID END
测试:
SELECT * FROM dbo.VERSION_CONTROL_TABLE
注释: 文章灵感来自: http://www.cnblogs.com/unruledboy/archive/2011/12/05/SQLMon2.html
和 http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes