自动记录数据库存储过程的更新和改变，并产生日志
一、 简介
使用环境：SQL Server
在项目开发过程中，项目管理者通常都很希望对项目的开发进展有一个日志的记录。代码的记录和管理可以通过TFS或者VSS等工具去管理。但是数据库却没有记录开发日志这一功能。这在实际开发中很不方便，特别是大量的存储过程改动。
那么针对这一个需求，在数据库中定时执行一个Job，让其自动记录存储过程的改动，以完成对数据库存储过程改动的一个记录。
二、 实际效果
|
Sp_id |
change_date |
action_type |
sp_name |
|
755585830 |
2007-11-13 |
Added |
Sp_a |
|
451584747 |
2007-11-13 |
Update |
Sp_b |
|
2119014630 |
2007-11-13 |
Update |
Sp_c |
|
2119014630 |
2007-11-13 |
Update |
Sp_d |
|
771585887 |
2007-11-13 |
Deleted |
Sp_e |
三、 实现原理
由于数据库中所有的存储过程代码都记录在系统表Sys_comments中，所以我们可以通过比较不同时间点代码的方式去实现记录一定时间范围内的存储过程变化。
比如：在今天早上01：00我们把所有存储过程代码从系统表中取出，并记录。然后到明天早上再重复这一过程，将两次取出的不同结果进行比较，取出有变化的存储过程并记录，认为这些存储过程在这一时间范围内存在更新。
四、 实现代码
其实原理很简单，就是一个循环比较的方式，所以也不需要大费周章的去介绍了，那么下面就把实现相关的代码和流程贴出来。
1建立数据表
在数据库建立三张表，sps_old, sps_new, sps_log。
Sps_old：记录前一次系统中所有存储过程代码，以备比较
Sps_new：当前所有存储过程的代码，与sps_log_old中的数据比较
Sps_log：记录在两个时间点范围内变化的存储过程名字和变化时间
表结构：
/****** Sps_log ******/
CREATE TABLE [dbo].[sps_log](
[sp_id] [int] NULL,
[change_date] [datetime] NULL,
[action_type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sp_name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
/****** Sps_new ******/----------------------------------------------------------------
CREATE TABLE [dbo].[sps_new](
[sp_id] [int] NULL,
[colid] [smallint] NULL,
[sp_name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sp_content] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[record_date] [datetime] NULL
) ON [PRIMARY]
/******Sps_old******/-----------------------------------------------------------------------------------
CREATE TABLE [dbo].[sps_old](
[sp_id] [int] NULL,
[colid] [smallint] NULL,
[sp_name] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sp_content] [varchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[record_date] [datetime] NULL
) ON [PRIMARY]
----------------------------------------------------------------------------------------------------------------
2建立进行比较用的存储过程，并记录结果集
/*------------------------------------------------------------------------------------------------------
-- Function : sps_log_add
-- Description : record all sps change log
--1 get all new sps (find sps which in new table not in old table)
--2 get all deleted sps (find sps which in old table not in new table)
--3 get all updated sps (find sps which in old talbe are not same in new table)
-- any change will record into sps_log table
------------------------------------------------------------------------------------------------------*/
CREATE procedure [dbo].[sps_log_add]
as
-- clear out sps_new
truncate table sps_new
-- fill into sps_new
insert into sps_new
(sp_id,colid,sp_name,sp_content,record_date)
select
a.id,
a.colid,
b.name,
a.[text],
getdate()
from
syscomments a
left join
sysobjects b
on a.id = b.id
where b.type='P' and b.name not like 'dt_%'
-- Find new sp
insert into
sps_log
select distinct
sp_id,
getdate(),
'Added',
sp_name
from
sps_new
where
sp_id not in (select distinct sp_id from sps_old)
-- Find deleted sp
insert into
sps_log
select distinct
sp_id,
getdate(),
'Removed',
sp_name
from
sps_old
where
sp_id not in (select distinct sp_id from sps_new)
-- compare existing sp
DECLARE @ProcID int
declare @count_new int
declare @count_old int
declare @text_new varchar(4000)
declare @text_old varchar(4000)
declare @name varchar(150)
declare @i int
DECLARE SearchProc CURSOR FOR
select distinct
sp_id
from
sps_new
where
sp_id in (select distinct sp_id from sps_old)
order by
sp_id
open SearchProc
FETCH NEXT FROM SearchProc
INTO @ProcID
WHILE @@FETCH_STATUS >=0
BEGIN
-- colid quantity
select @count_new=count(colid) from sps_new where sp_id = @ProcID
select @count_old=count(colid) from sps_old where sp_id = @ProcID
-- if count of colid is unequal, sp was updated.
if @count_new <> @count_old
begin
-- Record change
insert into sps_log(sp_id,change_date,action_type) values(@ProcID,getdate(),'Update')
end
else -- @count_new = @count_old, if count of colid is equal
begin
set @i=1 -- Reset Counter
while @i<=@count_new -- colid
begin
-- sp content
select @text_new = sp_content from sps_new
where sp_id = @ProcID and colid = @i
select @text_old = sp_content from sps_old
where sp_id = @ProcID and colid = @i
-- if content is different, sp was updated.
if @text_new <> @text_old
begin
-- Record change
select @name = [name] from sysobjects where id=@ProcID
insert into sps_log(sp_id,change_date,action_type,sp_name)
values(@ProcID,getdate(),'Update',@name)
end
set @i= @i+1 -- Counter + 1
end
end
FETCH NEXT FROM SearchProc
INTO @ProcID
END
CLOSE SearchProc
DEALLOCATE SearchProc
-- clear out sps_new
truncate table sps_old
-- fill into sps_old with sps_new
insert into sps_old select * from sps_new
----------------------------------------------------------------------------------------------------------------------
3创建一个存储过程从sps_log中获取指定时间内的数据
/*
-- Function : sps_log_get
-- Description : Show sps change in a period of time
*/
CREATE PROCEDURE [dbo].[sps_log_get]
@from_date datetime,
@to_date datetime
as
select
sp_name,
action_type,
CONVERT(varchar(10),change_date,102) as change_date
from
sps_log
where
change_date between @from_date and @to_date
group by
sp_name,action_type,CONVERT(varchar(10),change_date,102)
order by
CONVERT(varchar(10),change_date,102) asc
五、 实际应用和配置
在数据库中新建一个Job，代码为exec sps_log_add，一般运行可以设置为每天的零晨，一天一次比较合理，这样便能把每天做的改动记录下来。
六、 查看日志
直接运行存储过程exec sps_log_get ‘2007-11-01’,’2007-11-30’。便能查看所有在这一段时间内的存储过程变化。
如果您有更好的办法，还请务必留言。谢谢！
