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]

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

优质内容筛选与推荐>>
1、线性表元素的区间删除
2、GET和POST的比较
3、Bootstrap 进度条
4、比尔盖茨：十条“金口玉言”－－ 世界不会在意你的自尊
5、CSS设计网页时的一些常用规范