轉:自動記錄資料庫預存程序的更新和改變，併產生日誌

轉:http://www.cnblogs.com/Carlwave/archive/2007/11/19/964216.html 自動記錄資料庫預存程序的更新和改變，併產生日誌

 

一、        簡介

使用環境：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’。便能查看所有在這一段時間內的預存程序變化。