Go: Automatically record the updates and changes to the database stored procedures and generate a log

Goto: Http://www.cnblogs.com/Carlwave/archive/2007/11/19/964216.html automatically records updates and changes to the database stored procedures and generates logs

One, Brief Introduction

Working environment: SQL Server

In the process of project development, project managers often want to have a log of the development progress of the project. The recording and management of code can be managed by tools such as TFS or VSS. However, the database does not record the development log feature. This is inconvenient in actual development, especially for a large number of stored procedure changes.

For this requirement, a job is scheduled to be executed in the database to automatically record changes to the stored procedure to complete a record of changes to the database stored procedure.

Second, Actual effect

sp_id	change_date	action_type	sp_name
755585830	2007-11-13	Added	sp_a
45158 4747	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

Third, Implementation Principle

Because all the stored procedure code in the database is recorded in the system table sys_comments, we can realize the change of stored procedure in a certain time range by comparing the different time point code.

For example: At 01:00 today we take all the stored procedure code out of the system table and record it. Then repeat the process tomorrow morning, comparing the different results of the two removal, taking out the changed stored procedures and recording that the stored procedures are updated within this time span.

Four, Implementation Code

In fact, the principle is very simple, is a cyclical comparison of the way, so do not need to go to the trouble to introduce, then the implementation of the relevant code and procedures posted.

1 set up a data table

 

Build three tables in the database, Sps_old, Sps_new, Sps_log.

Sps_old: Record all stored procedure code in the previous system for comparison

Sps_new: Code for all current stored procedures, compared to data in Sps_log_old

Sps_log: Records the name and time of the stored procedure changing at two points in time

Table structure:

/****** Sps_log ******/

CREATE TABLE [dbo]. [Sps_log] (

[SP_ID] [INT] Null

[Change_date] [DateTime] Null

[Action_type] [Char] (a) COLLATE sql_latin1_general_cp1_ci_as NULL,

[Sp_name] [varchar] (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] (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] (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 establish a stored procedure for comparison and record the result set

 

/*------------------------------------------------------------------------------------------------------

--Function:sps_log_add

--Description:record all SPS change Log

--1 get "All new SPS" (find SPS which in new table not as old table)

--2 Get all deleted SPSS (find SPS which in old table not in new table)

--3 get all updated SPSS (find SPS which in old Talbe are not same in new table)

--Any change would 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 create a stored procedure from Sps_log gets the data in the specified time

 

/*

--Function:sps_log_get

--Description:show SPS change in a period of

*/

CREATE PROCEDURE [dbo]. [Sps_log_get]

@from_date datetime,

@to_date datetime

As

Select

Sp_name,

Action_type,

CONVERT (varchar (), 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), change_date,102)

ORDER BY

CONVERT (varchar (), change_date,102) ASC

Five, actual application and configuration

Create a new job in the database, the code is exec Sps_log_add, the general run can be set to 0 mornings a day, more reasonable once a day, so that the changes can be recorded every day.

Six, View Log

Run the stored procedure directly exec sps_log_get ' 2007-11-01 ', ' 2007-11-30 '. You can see all the changes in the stored procedures over time.