分析数据库的一些方法

在工作中，我们有时需要分析一个现有软件的数据库结构，简单的说，就是想知道两点

1 、各种数据保存在哪个表
2 、在什么情况下，表中的数据会发生更新

下面我把自己的方法写出来，如果您有更好的方法，请与我讨论。

1、为数据库中的每一个业务表建立对应的更新表
   当相应业务表的数据被更新时，触发器会把更新的类型和记录写进相应的更新表
   更新表的字段除了包括相应业务表的所有字段，还添加了三个字段

   (1) 一个自增的ID
   (2) 更新类型(I 插入;D 删除;U 更新)
   (3) 更新时间
  

2、在数据库中建立一个总更新表

   当任何一个业务表的数据被更新时，触发器会把更新的类型和表名写进总更新表，作用是快速找到当前发生数据更新的表
   总更新表有四个字段

   (1) 一个自增的ID
   (2) 更新类型(I 插入;D 删除;U 更新)
   (3) 更新的表名
   (4) 更新时间

3、为每一个业务表建立三个触发器，分别对应插入、删除、修改三种操作
当业务表发生更新时，会把更新前的记录、更新后的记录、删除的记录、插入的记录写入相应更新表

为此我专门写了两个存储过程，适用于SQL Server 2000，如果您的数据库不是SQL Server 2000，也可供您参考
为了新建立的表和触发器和数据库中原有的表和触发器同名，采用了加后缀方法，比如
表名为 Users的表，相应的更新表为Users+后缀，当后缀为_1234567时，更新表的表名为Users_1234567

下面是存储过程P_Analysis和P_ClearUp的脚本

/*=========================================================================
存储过程 P_Analysis
作用
为分析建立一个总的更新表 UPDATE+后缀+后缀
为每个表建立一个更新表   原表名+后缀
为每个表建立三个触发器   TR_表名_+触发器类型(I:插入 D:删除 U:更新)+后缀

输入参数  @postfix,以免分析用表和业务表名称重复，分析用触发器和原由触发器重复
使用举例  EXEC P_Analysis @#_1234567@#
============================================================================*/

CREATE PROCEDURE P_Analysis
 @postfix char(8)
AS
--测试是否会和数据库原有的对象名(字段名)重复
if exists(SELECT * from sysobjects where right(name,8)=@postfix) OR exists(SELECT * from syscolumns where

right(name,8)=@postfix)
  print @#对象名重复，请使用不同的后缀民名@#
else
 begin
   --为每个表建立更新记录表
   declare @TableName nvarchar(128)
   declare @columns varchar(8000)
   declare cur INSENSITIVE cursor
   FOR
   SELECT name from sysobjects where xtype=@#U@# and status>0
   OPEN cur
   FETCH NEXT FROM cur INTO @TableName
   while(@@fetch_status=0)
   BEGIN
        set @columns=@#@#
 --建立更新表
        EXEC(@#SELECT * into @#+@TableName+@postfix+@# FROM @#+@TableName+@# WHERE 1=0@#)
 --为更新表增加三个字段
        EXEC(@#alter table @#+@TableName+@postfix + @# add ID@#+@postfix+@# INT IDENTITY(1,1),OprType@#+@postfix+@#

char(2),OprTime@#+@postfix+@# datetime default getdate()@#)
        --为每个业务表建立三个触发器
        SELECT @columns=@columns+@#,@#+name from syscolumns where ID=object_id(@TableName)

 --插入触发器
 EXEC(@#CREATE TRIGGER TR_@#+@TableName+@#_I@#+@postfix+@# ON @#+@TableName+@# FOR INSERT AS@#+
        @# INSERT UPDATE@#+@postfix+@postfix+@#(TableName,OprType)@#+
        @# VALUES(@#@#@#+@TableName+@#@#@#,@#@#I@#@#)@#+
        @# INSERT @#+@TableName+@postfix+@#(OprType@#+@postfix+@columns+@#)@#+
        @# SELECT @#@#I@#@#@#+@columns+@# FROM INSERTED@#)

        --删除触发器
 EXEC(@#CREATE TRIGGER TR_@#+@TableName+@#_D@#+@postfix+@# ON @#+@TableName+@# FOR DELETE AS@#+
        @# INSERT UPDATE@#+@postfix+@postfix+@#(TableName,OprType)@#+
        @# VALUES(@#@#@#+@TableName+@#@#@#,@#@#D@#@#)@#+
        @# INSERT @#+@TableName+@postfix+@#(OprType@#+@postfix+@columns+@#)@#+
        @# SELECT @#@#D@#@#@#+@columns+@# FROM DELETED@#)

        --更新触发器
        EXEC(@#CREATE TRIGGER TR_@#+@TableName+@#_U@#+@postfix+@# ON @#+@TableName+@# FOR UPDATE AS@#+
        @# INSERT UPDATE@#+@postfix+@postfix+@#(TableName,OprType)@#+
        @# VALUES(@#@#@#+@TableName+@#@#@#,@#@#U@#@#)@#+
        @# INSERT @#+@TableName+@postfix+@#(OprType@#+@postfix+@columns+@#)@#+
        @# SELECT @#@#BU@#@#@#+@columns+@# FROM DELETED@#+
        @# INSERT @#+@TableName+@postfix+@#(OprType@#+@postfix+@columns+@#)@#+
        @# SELECT @#@#AU@#@#@#+@columns+@# FROM INSERTED@#)

  fetch next from cur into @TableName
   END
   CLOSE cur
   DEALLOCATE cur
   --建立总记录更新表
   EXEC(@#CREATE TABLE UPDATE@#+@postfix+@postfix+@#(ID numeric(18,0) IDENTITY(1,1),TableName varchar(256),OprType

char(1),OprTime datetime default GETDATE())@#)
END
GO

/*==================================================================
存储过程 P_ClearUp

作用:清除新建的表\触发器

输入参数: @postfix 默认值 _1234567

使用例子: 使用举例 EXEC P_ClearUp @#_1234567@#
====================================================================*/
CREATE PROCEDURE P_ClearUp
@postfix char(8)=@#_1234567@#
AS
--删除总更新表

   EXEC(@#if exists (select * from sysobjects where name =@#@#UPDATE@#+@postfix+@postfix+@#@#@# AND type=@#@#U@#@#)@#+
        @#DROP TABLE UPDATE@#+@postfix+@postfix)
   declare @TableName nvarchar(128)
   declare cur cursor
   FOR
   SELECT name from sysobjects where xtype=@#U@# and status>0
   OPEN cur
   FETCH NEXT FROM cur INTO @TableName
   while(@@fetch_status=0)
   BEGIN
 --删除更新表
        EXEC(@#if exists (select * from sysobjects where name =@#@#@#+@TableName+@postfix+@#@#@# AND type=@#@#U@#@#)@#+
             @#DROP TABLE @#+@TableName+@postfix)
 --删除插入触发器
        EXEC(@#if exists (select * from sysobjects where name =@#@#TR_@#+@TableName+@#_I@#+@postfix+@#@#@# AND type=@#@#TR@#@#)@#+
     @#DROP TRIGGER TR_@#+@TableName+@#_I@#+@postfix)
 --删除删除触发器
        EXEC(@#if exists (select * from sysobjects where name =@#@#TR_@#+@TableName+@#_D@#+@postfix+@#@#@# AND type=@#@#TR@#@#)@#+
      @#DROP TRIGGER TR_@#+@TableName+@#_D@#+@postfix)
 --删除更新触发器
        EXEC(@#if exists (select * from sysobjects where name =@#@#TR_@#+@TableName+@#_U@#+@postfix+@#@#@# AND type=@#@#TR@#@#)@#+
     @#DROP TRIGGER TR_@#+@TableName+@#_U@#+@postfix)
  fetch next from cur into @TableName
   END
   CLOSE cur
   DEALLOCATE cur
GO

原文转自：http://www.ltesting.net