Sql Server 2005 自动生成修改表的代码(存储过程)

我们公司的数据库也真是多啊，大大小小的有30多个，大一点的分公司都有自己独立的数据库，然后有些库还需要有给客户和供应商以及自己内部新员工的 Test 库，还有要给开发部门的 develop 库，这些库的结构当然还要求是一样的，因为很多数据是需要上传到集团总部去的，特别是人事和财务方面的数据。而以前的设计跟现在的实际需求相差也有点大的，所以修改表结构的事情几乎每天都有发生，公司上层一个简单的决定真是难为死我这个做数据库维护的人了。

很多时候要增加的字段必须放在要求的位置上，不能放最后，有些字段一需要其他所有关联的字段也有修改，还是所有的数据库中有的都必须修改。当然这都不是很难，都是很简单的事情，可是一个个的改对我懒人高升来说真是莫大的痛苦，所以我又想出了一个偷懒的办法，写了一个修改表的存储过程，比原先写的那个各有各的好处，这个可是模仿 MS 在企业管理器中拖拖鼠标自动生成的代码写的。

原先那个存储过程：http://blog.csdn.net/hb_gx/archive/2007/06/18/1655990.aspx

测试的效果还是很不错的，高兴！虽然又浪费我一个双休，可是今天上班真是轻松啊，旁边那个高级一点的DBA自己点鼠标点的只响也不怕麻烦，想我高升这辈子偷懒也是偷出了点小名堂的。：）

本存储过程能把某个数据库的表结构复制到指定的数据库同名表中，但是不改目标数据库中的数据，原始数据还是保留的，跟数据库复制是有区别的，如果你有两个数据库是一样的，只是数据不同，那么当其中一个修改了结构后可以使用本存储过程帮你自动更新另一个结构。其实就是和在企业管理器中拖拖鼠标一样的，只是可以换到别的数据库生成。

USE  msdb
GO
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO
-- 建立人：  高升
-- 建立日期：2007/06/25
-- 修改日期：2007/08/01
-- 功能目的：复制源DB中某个表的结构到目标DB的同名表中，只改表结构、约束、索引等，不改目标DB的数据
-- 注意：    默认约束名是自动生成的新名字，如果和以前的名字有冲突请手工修改后运行，
--           新的默认约束名统一为 'DF_表名_列名' ,如果要使用原默认约束名需要修改代码
ALTER   PROCEDURE   [ dbo ] . [ AlterTableLayout ]
   @sourceDB                  sysname,         -- 源DB名
   @targetDB                  sysname,         -- 目标DB名
   @schemaName                sysname  =   ' dbo ' , -- 架构名,此参数保留，未使用
   @sourceTableName           sysname,         -- 源表名
   @targetTableName           sysname  =   '' ,    -- 目标表名,默认与源表名相同
   @enable                     bit   =   0           -- 是否执行
WITH  ENCRYPTION
AS
DECLARE   @schema_id            int               -- 架构ID
DECLARE   @tmpTableName         varchar ( 100 )     -- 临时表名
DECLARE   @columnName           varchar ( 100 )     -- 列名
DECLARE   @d_name               varchar ( 100 )     -- 默认约束的约束名
DECLARE   @definition           varchar ( 100 )     -- 默认值
DECLARE   @i_name               varchar ( 100 )     -- 索引名
DECLARE   @is_key               bit               -- 是否主键
DECLARE   @i_no                 tinyInt           -- 索引的序号
DECLARE   @c_name               varchar ( 200 )     -- 索引所在的列名
DECLARE   @i_type               varchar ( 60 )      -- 是否聚集
DECLARE   @is_unique            varchar ( 6 )       -- 是否唯一
DECLARE   @is_unique_key        bit               -- 是否唯一键
DECLARE   @cmd_all              varchar ( max )     -- 存放全部语句
DECLARE   @cmd_temp             nvarchar ( max )    -- 存放临时执行的语句
DECLARE   @cmd_create_table     varchar ( 5000 )    -- 存放创建 Table 的语句
DECLARE   @cmd_drop_default     nvarchar ( max )    -- 删除默认约束
DECLARE   @cmd_add_default      nvarchar ( max )    -- 添加默认约束
DECLARE   @cmd_add_index        varchar ( 2000 )    -- 添加索引
DECLARE   @cmd_add_check        varchar ( 2000 )    -- 添加 CHECK 约束
DECLARE   @cmd_add_foreign      varchar ( 600 )     -- 添加外键约束
DECLARE   @cmd_insert           varchar ( max )     -- 插入语句
DECLARE   @c_name_A             varchar ( 4000 )    -- INSERT语句用
DECLARE   @c_name_B             varchar ( 4000 )    -- INSERT语句用
DECLARE   @identity_on          varchar ( 60 )      -- 关闭自增长
DECLARE   @identity_off         varchar ( 60 )      -- 开启自增长
DECLARE   @cmd_create_trigger   nvarchar ( max )    -- 创建 TRIGGER 的语句
DECLARE   @i                    smallInt          -- 用于循环

SET  NOCOUNT  ON

IF  ( @targetTableName   =   '' )  SET   @targetTableName   =   @sourceTableName
SET   @schema_id   =  SCHEMA_ID( @schemaName )
SET   @columnName   =   ''
SET   @cmd_add_default   =   ''
SET   @cmd_drop_default   =   ''
SET   @cmd_add_index   =   ''

DECLARE   @columns   table (c_no  int   identity ,c_name  varchar ( 100 )) -- 存放表中所有的列名
DECLARE   @indexName   table (i_no  tinyInt   IDENTITY ( 1 , 1 ),          -- 存放该表中的索引名
    i_name  varchar ( 100 ),type_desc  varchar ( 60 ),is_unique  bit ,is_key  bit ,is_unique_key  bit )
INSERT   INTO   @columns   EXEC ( ' SELECT name FROM  '   +   @sourceDB   +   ' .sys.columns WHERE object_id = (SELECT object_id FROM  '   +   @sourceDB   +   ' .sys.tables WHERE name =  '''   +   @sourceTableName   +   ''' ) ' )
INSERT   INTO   @indexName   EXEC ( ' SELECT name,type_desc,is_unique,is_primary_key,is_unique_constraint FROM  '   +   @sourceDB   +   ' .sys.indexes WHERE object_id = (SELECT object_id FROM  '   +   @sourceDB   +   ' .sys.tables WHERE name =  '''   +   @sourceTableName   +   ''' ) ' )

-- 生成中间过渡临时表的名字
SET   @cmd_temp   =   ' DECLARE @i tinyInt
SET @i = 1
SET @tmpTableName =  '&