存储过程实现数据库重构、变更的一个案例

当前流行的演进式的软件开发模式，使数据库的重构和变更成为软件构建过程中的平常事情，当然数据库的重构和变更涉及到许多设计和项目管理上的东西，我这里只是给出一个我遇到的关于数据库变更的案例。

需求过程是这样的：在数据库设计已经大体完成，用户突然提出要加强系统的数据跟踪的能力，于是设计者要求在现在所有的数据表里添加三列属性col1，col2，col3以应对客户的这种需求，由于负责这件事情的数据库管理员的疏忽，把这三列设置成not null类型的，并且执行了满足这个需求的脚本。但是有的小组已经开始了数据库的单元测试，显然这三列在不设置默认值的前提下，这个脚本的执行导致数据库的有些数据表存在这三列，并且是not null类型，而有的数据表没有这三列。设计者决定修改这三列是可以为null的，而不设置默认值。预期的数据库是所有的数据表都有col1，col2，col3三列，并且是可为null的。显然现在我们面对着一个乱七八糟的数据库。如何解决这个问题呢？
1 如果在数据库变更前存有最近的备份，就还原数据库，修改执行脚本的列可为null的就可以了。
2 如果恰巧数据库管理员没有及时的备份，那就得根据情况重新编写执行脚本了。在这种情况下可以遍历所有的用户表，检测每个表是否有col1，col2，col3，如果存在，修改列的值可以是null，如果不存在则添加col1，col2，col3，并设置列的值可为null。示例代码如下：

declare @tablename nvarchar(50) 
declare @sql1 nvarchar(200) 
declare @sql2 nvarchar(200)
declare cur cursor for
select Name from sysobjects where xtype='u' and status>=0 order by name
open cur
---遍历所有的表
FETCH NEXT FROM cur into @tablename
--set @@tablename=convert(
WHILE @@FETCH_STATUS =0
   BEGIN
   --检测是否存在col1，col2，col3列
    if not exists( select * from syscolumns where id =object_id(@tablename) and (name='col2' or name='col3' or name='col1'))
    begin
    set @sql1=N'alter table '+@tablename+' add col1 varchar(50) null,col2 varchar(50) null,col3 varchar(50) null'
   print @sql1
    exec sp_executesql @sql1
    end
    ---存在列，修改列，使其值可为null
    else
      begin
       set @sql2=N'alter table '+ @tablename +' alter column col1 varchar(50) null'
       exec sp_executesql @sql2
       set @sql2=N' alter table '+ @tablename +' alter column col2 varchar(50) null'
       exec sp_executesql @sql2
       set @sql2=N'alter table '+ @tablename +' alter column col3 varchar(50) null'
       exec sp_executesql @sql2
       print @sql2
       exec sp_executesql @sql2
      end
--print @tablename
     FETCH NEXT FROM cur into @tablename
   END
CLOSE cur
DEALLOCATE cur

 

很显然以上两种方式，第一种方式最为安全，简单。这就要求数据库管理员要及时备份数据库，特别是数据库重构，变更前。注重数据库的版本控制，对每次重构或变更的执行版本要和数据库版本联系起来。