[MS SQL] - 存储过程、函数、视图、触发器的修改日志

存储过程、函数、日志可以多人修改，为了方便追溯与找回原来脚本，建日志表

CREATE TABLE [dbo].[sysObjectModifyLog](
	[objName] [varchar](1000) NULL,
	[contents] [nvarchar](max) NULL,
	[checkdate] [datetime] NULL,
	[modifydate] [datetime] NULL,
	[versions] [int] NULL,
	[objectid] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

建存储过程

/*
* Create 
* 描述: 检查数据库对象是否有改动，有改动就记录改动日志
         由作业调用，  时间间隔越短 ，改动记录越准


		 exec dbo.USP_sysCheckObjectModify
*/

ALTER PROCEDURE [dbo].[USP_sysCheckObjectModify]
AS
BEGIN
    

    SELECT  iden = identity(int,1,1), 
	   a.[name] , 
       a.create_date , 
       a.modify_date,
	   a.object_id 
	INTO #tempObj
    FROM    sys.all_objects  a
	   LEFT JOIN sysObjectModifyLog b on b.objectid = a.object_id AND versions = (SELECT MAX(versions) as versions FROM sysObjectModifyLog where objectid = b.objectid)
    WHERE   type_desc in (N'SQL_STORED_PROCEDURE','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION','VIEW','SQL_TRIGGER') 
	       and a.modify_date > isnull(b.modifydate,'1900-1-1') and a.object_id >0
    ORDER BY modify_date DESC 
    
	DECLARE @j INT,@object_id BIGINT,@modifydate datetime ,@versions int

    SELECT @j = 1,@object_id= 0,@versions = 1

    WHILE exists(SELECT 1 FROM #tempObj WHERE iden = @j)
    BEGIN

        SELECT @versions = 1

        SELECT @object_id = object_id,@modifydate = modify_date FROM #tempObj WHERE iden = @j
    
	    SELECT @versions = ISNULL(MAX(versions),0) +1 FROM sysObjectModifyLog WHERE objectid = @object_id

	    DECLARE @i int
        SET @i = 1

        WHILE exists(select 1 from syscomments where id = @object_id and colid = @i )
        BEGIN
        
            IF not exists (select 1 from sysObjectModifyLog  where objectid = @object_id  and versions = @versions) 
	        BEGIN
                INSERT INTO sysObjectModifyLog(objName,contents,checkdate,modifydate,versions,objectid)
	       
		        SELECT b.name,a.[text],getdate(),@modifydate,@versions,@object_id
                FROM syscomments a
                    left join sysobjects b on a.id = b.id
                WHERE a.id = @object_id and a.colid = @i
            END
	        ELSE 
	        BEGIN
	            UPDATE a
                SET a.contents = a.contents + (select text  from syscomments  where id = @object_id and  colid = @i)
	    	    FROM sysObjectModifyLog a
		        WHERE a.objectid = @object_id and versions  = @versions
	        END
            
			SET @i = @i + 1
        END

        SET @j = @j + 1
    END

END

上面过程建立作业，定时执行对比修改前后内容；

问题：

  时间间隔中间有多次修改无法记录。