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

本文介绍了如何使用SQL Server 2005的存储过程来自动化修改表结构,实现数据库间表结构的同步,而不影响原始数据。作者分享了一个自创的存储过程,其功能类似于在企业管理器中手动操作,适用于需要频繁更新表结构的场景。文中还提到了可能遇到的问题,如外键、触发器和字段长度,并提醒读者根据实际需求调整。经过两个月的测试,该存储过程被证实可以稳定运行。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

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

原先那个存储过程:http://blog.youkuaiyun.com/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 = 
'' Tmp_ '   +   @targetTableName   +   '''
WHILE (EXISTS (SELECT * FROM 
'   +   @targetDB   +   ' .sys.objects WHERE NAME = @tmpTableName AND TYPE =  '' U '' ))
BEGIN
SET @tmpTableName = 
'' Tmp_ '   +   @targetTableName   +   ' _ ''  + RTRIM(@i)
SET @i = @i + 1
END
'
EXECUTE  sp_executesql  @cmd_temp ,N ' @tmpTableName varchar(100) OUTPUT ' , @tmpTableName  OUTPUT

-- 生成创建表的代码
SET   @cmd_temp   =   ' USE  '   +   @sourceDB   +   '
SET @cmd_create_table = 
'' CREATE TABLE  '   +   @tmpTableName   +   ' ( ''   + char(13)
SELECT @cmd_create_table = @cmd_create_table + 
'' [ ''  + name +  '' ''  + 
CASE is_computed WHEN 1 THEN
    (SELECT 
'' AS  ''  + definition + CASE is_persisted WHEN  '' 1 ''  THEN  ''  PERSISTED ''  ELSE  ''''  END
       FROM sys.computed_columns WHERE name = sys.columns.name)
ELSE (TYPE_NAME(system_type_id) +
  CASE WHEN system_type_id in (167,175) THEN 
'' ( ''  + (CASE max_length WHEN -1 THEN  '' MAX ''  ELSE RTRIM(max_length) END) +  '' ) ''
    WHEN system_type_id in (231,239) THEN 
'' ( ''  + (CASE max_length WHEN -1 THEN  '' MAX ''  ELSE RTRIM(max_length / 2) END) +  '' ) ''
    WHEN system_type_id in (106,108) THEN 
'' ( ''  + RTRIM(precision) +  '' , ''  + RTRIM(scale) +  '' ) ''
    ELSE 
''''  END +
  CASE is_nullable WHEN 0 THEN 
''  NOT NULL ''
    ELSE 
''''  END +
  CASE is_identity WHEN 1 THEN
    (SELECT TOP 1 
''  IDENTITY( ''  + CAST(seed_value as varchar(10)) +  '' , ''  + CAST(increment_value as varchar(10)) +  '' ) ''
       FROM sys.identity_columns WHERE name = sys.columns.name)
  ELSE 
''''  END)
END + 
'' , ''  + char(13) FROM sys.columns where object_id = OBJECT_ID( '''   +   @sourceTableName   +   ''' )
SET @cmd_create_table = SUBSTRING(@cmd_create_table,1,LEN(@cmd_create_table) - 2) + 
'' )
GO
'''
EXEC  sp_executesql  @cmd_temp ,N ' @cmd_create_table varchar(5000) OUTPUT ' , @cmd_create_table  OUTPUT

-- 生成创建和删除默认约束的代码
SET   @i   =   1
WHILE  ( @i   <=  ( SELECT   COUNT (c_no)  FROM   @columns ))
BEGIN
  
SELECT   @columnName   =  c_name  FROM   @columns   WHERE  c_no  =   @i
  
SET   @cmd_temp   =   ' use  '   +   @sourceDB   +   char ( 13 +
    
' SELECT @d_name = name,@definition = definition FROM sys.default_constraints
      WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID(
'''   +   @sourceTableName   +   ''' ) AND name =  '''   +   @columnName   +   ''' ) '
  
SET   @d_name   =   ''
  
EXEC  sp_executesql  @cmd_temp ,N ' @d_name varchar(60) output,@definition nvarchar(50) output ' , @d_name  OUTPUT, @definition  OUTPUT
  
IF  ( @d_name   !=   '' )     -- 此处使用了新默认约束名,原默认约束名保存在 @d_name 中没有使用
     SET   @cmd_add_default   =   @cmd_add_default   +   ' ALTER TABLE [ '   +   @tmpTableName   +   ' ] ADD CONSTRAINT [DF_ '   +   @targetTableName   +   ' _ '   +   @columnName   + ' ] DEFAULT  '   +   @definition   +   '  FOR [ '   +   @columnName   +   ' ]
GO
'
  
SET   @cmd_temp   =   ' use  '   +   @targetDB   +   char ( 13 +
    
' SELECT @d_name = name FROM sys.default_constraints
      WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID(
'''   +   @targetTableName   +   ''' ) AND name =  '''   +   @columnName   +   ''' ) '
  
SET   @d_name   =   ''
  
EXEC  sp_executesql  @cmd_temp ,N ' @d_name varchar(60) output ' , @d_name  OUTPUT
  
IF  ( @d_name   !=   '' )
    
SET   @cmd_drop_default   =   @cmd_drop_default   +   ' ALTER TABLE [ '   +   @targetTableName   +   ' ] DROP CONSTRAINT [ '   +   @d_name   +   ' ]
GO
'
  
SET   @i   =   @i   +   1
END

-- 生成创建索引的代码,没有考虑填充因子等选项,使用的是默认值
SET   @i   =   1
WHILE  ( @i   <=  ( SELECT   COUNT (i_no)  FROM   @indexName ))
BEGIN
  
SELECT   @i_name   =  i_name, @i_type   =  type_desc, @is_unique   =  is_unique, @is_key   =  is_key  FROM   @indexName   WHERE  i_no  =   @i
  
IF  ( @i_name   is   null )     -- 如果没有索引或键直接退出
     BREAK ;
  
SET   @cmd_temp   =   ' set @c_name =  ''''
    SELECT @c_name = @c_name + 
'' ,[ ''  + a.name + (CASE b.is_descending_key WHEN 1 THEN  '' ] DESC ''  ELSE  '' ] ASC ''  END)
    FROM 
'   +   @sourceDB   +   ' .sys.columns a inner join  '   +   @sourceDB   +   ' .sys.index_columns b 
    ON a.object_id = b.object_id AND a.column_id = b.column_id inner join 
'   +   @sourceDB   +   ' .sys.indexes c
    ON b.object_id = c.object_id AND b.index_id = c.index_id
    WHERE a.object_id = (SELECT object_id FROM 
'   +   @sourceDB   +   ' .sys.tables WHERE name =  '''   +   @sourceTableName   +   ''' ) AND c.name =  '''   +   @i_name   +   ''''

  
EXEC  sp_executesql  @cmd_temp ,N ' @c_name varchar(200) output ' , @c_name  output

  
IF  ( @is_key   =   1 )     -- 键和索引的创建方法不一样
     SET   @cmd_add_index   =   @cmd_add_index   +   ' ALTER TABLE [ '   +   @targetTableName   +   ' ] ADD CONSTRAINT [ '   +   REPLACE ( @i_name , @sourceTableName , @targetTableName +   ' ] PRIMARY KEY  '   +   @i_type   +   char ( 13 +   ' ( '   +   SUBSTRING ( @c_name , 2 , len ( @c_name ))  +   ' ) '   +   '
GO
'
  
ELSE   IF  ( @is_unique_key   =   1 )   -- 唯一键
     SET   @cmd_add_index   =   @cmd_add_index   +   ' ALTER TABLE [ '   +   @targetTableName   +   ' ] ADD CONSTRAINT [ '   +   REPLACE ( @i_name , @sourceTableName , @targetTableName +   ' ] UNIQUE  '   +   @i_type   +   char ( 13 +   ' ( '   +   SUBSTRING ( @c_name , 2 , len ( @c_name ))  +   ' ) '   +   '
GO
'
  
ELSE    -- 普通索引
     SET   @cmd_add_index   =   @cmd_add_index   +   ' CREATE  '   +  ( CASE   @is_unique   WHEN   1   THEN   ' UNIQUE '   ELSE   ''   END +   '   '   +   @i_type   +   '  INDEX [ '   +   REPLACE ( @i_name , @sourceTableName , @targetTableName +   ' ] ON [ '   +   @targetTableName   +   ' ] '   +   char ( 13 +   ' ( '   +   SUBSTRING ( @c_name , 2 , len ( @c_name ))  +   ' ) '   +   '
GO
'
SET   @i   =   @i   +   1   -- 循环下一个键或索引
END   -- end while

-- 生成创建 CHECK 约束的代码
SET   @cmd_temp   =   ' SET @cmd_add_check =  ''''
SELECT @cmd_add_check = @cmd_add_check + 
'' ALTER TABLE [ '   +   @targetTableName   +   ' ] WITH NOCHECK ADD CONSTRAINT  ''  + name +  ''  CHECK  ''  + definition +  ''
GO
''  FROM  '   +   @sourceDB   +   ' .sys.check_constraints WHERE parent_object_id = (SELECT object_id FROM  '   +   @sourceDB   +   ' .sys.tables WHERE name =  '''   +   @sourceTableName   +   ''' ) '
EXEC  sp_executesql  @cmd_temp ,N ' @cmd_add_check varchar(2000) OUTPUT ' , @cmd_add_check  OUTPUT

-- 判断是否有自增长列
SET   @cmd_temp   =   ' IF EXISTS
 (SELECT name FROM 
'   +   @sourceDB   +   ' .sys.columns WHERE object_id = (SELECT object_id FROM  '   +   @sourceDB   +   ' .sys.tables WHERE name =  ''' +   @sourceTableName   +   ''' ) AND is_identity = 1)
BEGIN
  SET @identity_on = 
'' SET IDENTITY_INSERT [ '   +   @tmpTableName   +   ' ] ON
GO
''
  SET @identity_off = 
'' SET IDENTITY_INSERT [ '   +   @targetTableName   +   ' ] OFF
GO
''
END
ELSE
BEGIN
  SET @identity_on = 
''''
  SET @identity_off = 
''''
END
'
EXEC  sp_executesql  @cmd_temp ,N ' @identity_on varchar(60) OUTPUT,@identity_off varchar(60) OUTPUT ' , @identity_on  OUTPUT, @identity_off  OUTPUT

-- 生成创建外键约束的代码
SET   @cmd_temp   =   ' SET @cmd_add_foreign =  ''''
SELECT @cmd_add_foreign = @cmd_add_foreign + 
'' ALTER TABLE  '   +   @targetTableName   +   '  ADD CONSTRAINT  ''  + a.name +
    
''  FOREIGN KEY ( ''  + (SELECT name FROM  '   +   @sourceDB   +   ' .sys.columns WHERE OBJECT_ID = b.parent_object_id AND column_id = b.parent_column_id) +  '' ) REFERENCES  ''  + 
    (SELECT name FROM 
'   +   @sourceDB   +   ' .sys.tables WHERE object_id = a.referenced_object_id) +  '' ( ''  + (SELECT name FROM  '   +   @sourceDB   +   ' .sys.columns WHERE OBJECT_ID = b.referenced_object_id AND column_id = b.referenced_column_id) +  '' ) ON UPDATE  ''  +
    CASE update_referential_action WHEN 0 THEN 
'' SET NULL ''  WHEN 1 THEN  '' CASCADE ''  WHEN 2 THEN  '' NO ACTION ''  ELSE  '' SET DEFAULT ''  END +  ''  ON DELETE  ''  +
    CASE delete_referential_action WHEN 0 THEN 
'' SET NULL ''  WHEN 1 THEN  '' CASCADE ''  WHEN 2 THEN  '' NO ACTION ''  ELSE  '' SET DEFAULT ''  END +  ''
GO
''
  FROM 
'   +   @sourceDB   +   ' .sys.foreign_keys a INNER JOIN  '   +   @sourceDB   +   ' .sys.foreign_key_columns b ON a.object_id = b.constraint_object_id
 WHERE a.parent_object_id = (SELECT object_id FROM 
'   +   @sourceDB   +   ' .sys.tables WHERE name =  '''   +   @sourceTableName   +   ''' ) '
EXEC  sp_executesql  @cmd_temp ,N ' @cmd_add_foreign varchar(500) OUTPUT ' , @cmd_add_foreign  OUTPUT

-- 生成 INSERT 语句
SET   @cmd_temp   =   ' SELECT @c_A =  '''' , @c_B =  ''''
SELECT @c_A = @c_A + 
'' [ ''  + a.name +  '' ], '' ,@c_B = @c_B +
  CASE WHEN b.name IS NOT NULL THEN 
'' [ ''  + b.name  +  '' ] ''
  ELSE (CASE WHEN default_object_id != 0 THEN definition
    WHEN is_nullable = 0 THEN
      (CASE WHEN system_type_id in(48,52,56,59,60,62,106,108,122,127)THEN 
'' 0 ''  ELSE  ''''''''''''  END)
    ELSE 
'' NULL ''  END)END +  '' , ''
 FROM
  (SELECT a.name,a.system_type_id,a.is_nullable,a.default_object_id,b.definition FROM 
'   +   @sourceDB   +   ' .sys.columns a
     LEFT JOIN 
'   +   @sourceDB   +   ' .sys.default_constraints b ON a.object_id = b.parent_object_id AND a.default_object_id = b.object_id
    WHERE a.object_id = (SELECT object_id from 
'   +   @sourceDB   +   ' .sys.objects WHERE type =  '' U ''  AND name =  '''   +   @sourceTableName   +   '''  AND is_computed = 0))a
 LEFT JOIN
  (SELECT name FROM 
'   +   @targetDB   +   ' .sys.columns WHERE object_id = 
    (SELECT object_id from 
'   +   @targetDB   +   ' .sys.objects WHERE type =  '' U ''  AND name =  '''   +   @targetTableName   +   '''  AND is_computed = 0))b
  ON a.name = b.name
'
EXEC  sp_executesql  @cmd_temp ,N ' @c_A varchar(4000) OUTPUT,@c_B varchar(4000) OUTPUT ' , @c_name_A  OUTPUT, @c_name_B  OUTPUT
SET   @cmd_insert   =   CAST ( ' INSERT INTO [ '   as   varchar ( max ))  +   @tmpTableName   +   ' ]( ' +   SUBSTRING ( @c_name_A , 1 , LEN ( @c_name_A -   1 + ' )
    SELECT 
'   +   SUBSTRING ( @c_name_B , 1 , LEN ( @c_name_B -   1 +   '  FROM [ '   +   @targetTableName   +   ' ] WITH (HOLDLOCK TABLOCKX)
GO
'

-- 生成创建 TRIGGER 的语句
SET   @cmd_temp   =   ' SET @cmd_create_trigger =  ''''
SELECT @cmd_create_trigger = @cmd_create_trigger + 
''
exec(
''''''  + REPLACE(definition, '''''''' , '''''''''''' ) +  ''
'''' ); ''  FROM  '   +   @targetDB   +   ' .sys.sql_modules WHERE object_id in
(SELECT object_id FROM 
'   +   @targetDB   +   ' .sys.triggers WHERE parent_id = (SELECT object_id FROM  '   +   @targetDB   +   ' .sys.tables WHERE name =  '''   +   @targetTableName   +   ''' )) '
EXEC  sp_executesql  @cmd_temp ,N ' @cmd_create_trigger nvarchar(max) OUTPUT ' , @cmd_create_trigger  OUTPUT

-- 汇总所有的语句
SET   @cmd_all   =   ' BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
USE [
'   +   @targetDB   +   ' ]
GO
BEGIN TRANSACTION
GO
'   +   @cmd_drop_default   +   @cmd_create_table   +   @cmd_add_default   +   @identity_on   +   @cmd_insert   +
' DROP TABLE [ '   +   @targetTableName   +   ' ]
GO
EXECUTE sp_rename N
'''   +   @tmpTableName   +   ''' ,N '''   +   @targetTableName   +   ''' , '' OBJECT ''  
GO
'   +   @cmd_add_index   +   @identity_off   +   ' COMMIT '

-- 执行或打印生成的语句
IF  ( @enable   =   1 )
BEGIN
  
-- 批处理的时候去掉 @cmd_all 中的 'GO' 以后才能执行
   SET   @cmd_all   =   REPLACE ( @cmd_all ,( ' GO '   +   char ( 13 )), '' )
  
BEGIN   TRANSACTION
  
BEGIN  TRY
    
EXEC  ( @cmd_all )
    
IF  ( @cmd_add_check   +   @cmd_add_foreign   !=   '' )
    
BEGIN     -- 这里有点麻烦,必须把上面的建好才能建约束,不知道为什么
        SET   @cmd_all   =   ' USE [ '   +   @targetDB   +   ' ];
'   +   @cmd_add_check   +   @cmd_add_foreign
      
SET   @cmd_all   =   REPLACE ( @cmd_all ,( ' GO '   +   char ( 13 )), '' )
      
EXEC  ( @cmd_all )
    
END
    
IF  ( @cmd_create_trigger   !=   '' )   -- 如果表有触发器则继续添加触发器
     BEGIN
      
SET   @cmd_create_trigger   =   ' USE [ '   +   @targetDB   +   ' ];
'   +   @cmd_create_trigger
      
EXEC  ( @cmd_create_trigger )
    
END
    
COMMIT
  
END  TRY
  
BEGIN  CATCH
    
print  (ERROR_MESSAGE())
    
ROLLBACK
  
END  CATCH
END    -- end if
ELSE   IF  ( @enable   =   0 )
BEGIN
  
SET   @cmd_all   =   ' /*为了防止任何可能出现的数据丢失问题,您应该先仔细检查此脚本,然后再在数据库设计器的上下文之外运行此脚本。*/
'   +   @cmd_all   +   char ( 13 +   @cmd_create_trigger
  
PRINT   SUBSTRING ( @cmd_all , 1 , 8000 )   --  PRINT 一次最多打印8000个字符
   SET   @i   =   0
  
WHILE  ((( LEN ( @cmd_all -   @i   *   8000 /   8000 >   0 )
  
BEGIN   -- 如果输出的字符串大于8000则循环打印出来
     SET   @i   =   @i   +   1
    
PRINT   SUBSTRING ( @cmd_all , @i   *   8000   +   1 , 8000 )
  
END    -- end while
END    -- end else if
ELSE      -- 如果传入的是 null 则直接执行,抛出具体的错误信息,建议不要使用
BEGIN
  
SET   @cmd_all   =   REPLACE ( @cmd_all ,( ' GO '   +   char ( 13 )), '   ' +   @cmd_create_trigger
  
EXEC  ( @cmd_all )
END    -- end else
GO

SET  ANSI_NULLS  OFF
GO
SET  QUOTED_IDENTIFIER  OFF
GO

 

最后想了一想,发现外键这个东西不好加的,我一直不喜欢用,我们公司里所有的表都没有建立外键关系的,所以没有怎么测试的,使用的时候注意一下。还有如果表有触发器,修改的时候注意触发器中某些特定字符,可能会导致执行失败。另外定义的那些字段的长度并不是太好指定,如果全部都用varchar(max)好像也没那个必要的,如果遇到某些错误,可能是字段定义的长度问题,这个还是要根据实际情况了决定,定义多了也是浪费。个人认为一个表内字段可能有很多,相应的默认约束也会很多,但INDEX和CHECK约束通就常不会有很多了。

建立一个测试环境,测试一下,看看存储过程的效果:

-- 创建第一个测试库和一个表,有很多的类型和计算列约束之类的东东
CREATE   DATABASE  Test1
GO
USE  Test1
GO
CREATE   TABLE  dbo.T1
    (
    t2 
int   NULL ,
    t1 
uniqueidentifier   NOT   NULL ,
    t3 
char ( 4 NULL ,
    t4 
varchar ( 8 NOT   NULL ,
    t5 
decimal ( 6 2 NOT   NULL ,
    t6  
AS  ( [ t2 ] + [ t5 ] / ( 2 )),
    t7  
AS  ( [ t2 ] + [ t5 ] / ( 4 )) PERSISTED ,
    t8 
int   NOT   NULL   IDENTITY  ( 100 10 ),
    t9 
bigint   NOT   NULL
    )  
ON   [ PRIMARY ]
GO
ALTER   TABLE  dbo.Tmp_T1  ADD   CONSTRAINT
    DF_T1_t2 
DEFAULT  (( 0 ))  FOR  t2
GO
ALTER   TABLE  dbo.Tmp_T1  ADD   CONSTRAINT
    DF_T1_t4 
DEFAULT  ( '' FOR  t4
GO
ALTER   TABLE  dbo.Tmp_T1  ADD   CONSTRAINT
    DF_T1_t5 
DEFAULT  (( 10 ))  FOR  t5
GO
ALTER   TABLE  dbo.Tmp_T1  ADD   CONSTRAINT
    DF_T1_t9 
DEFAULT  (( 0 ))  FOR  t9
GO
ALTER   TABLE  dbo.T1  ADD   CONSTRAINT
    PK_T1 
PRIMARY   KEY   CLUSTERED  
    (
    t1
    ) 
WITH ( STATISTICS_NORECOMPUTE  =   OFF , IGNORE_DUP_KEY  =   OFF , ALLOW_ROW_LOCKS  =   ON , ALLOW_PAGE_LOCKS  =   ON ON   [ PRIMARY ]

GO
CREATE   NONCLUSTERED   INDEX  IX_T1  ON  dbo.T1
    (
    t3
    ) 
WITH ( STATISTICS_NORECOMPUTE  =   OFF , IGNORE_DUP_KEY  =   OFF , ALLOW_ROW_LOCKS  =   ON , ALLOW_PAGE_LOCKS  =   ON ON   [ PRIMARY ]
GO

-- 创建第二个测试库和一个表,就是一表,里面可以加点数据看看
CREATE   DATABASE  Test2
GO
USE  Test2
CREATE   TABLE  dbo.T1
    (
    t1 
uniqueidentifier   NOT   NULL ,
    t2 
int   NULL ,
    t4 
varchar ( 8 NOT   NULL ,
    t5 
decimal ( 6 2 NOT   NULL ,
    t6  
AS  ( [ t2 ] + [ t5 ] / ( 2 )),
    ) 
GO
-- 没加数据,可以随便加一点测试数据上去

执行看看,现在 Test2 里面的 T1 表变什么样子了,如果里面原来有数据的,看看现在的数据是不是一样啊?

-- 用刚建的存储过程测试看看,最后的1就直接执行了,如果想看生成的代码用0看看
EXEC  msdb.dbo.AlterTableLayout  ' Test1 ' , ' Test2 ' , 'dbo' , ' T1 ' ,'' , ' 1 '

唉!我高升也真是有点 BT 的,工作日的时间到处玩,一到双休就冒出来免费加班,为什么每次到双休才能写出点东西啊?真不知道该怎么说我了,表现给谁看啊!

本篇地址:http://blog.youkuaiyun.com/hb_gx/archive/2007/06/25/1666347.aspx

6月份写出来的东西,现在已经8月份了,经过两个月的测试,现在已经肯定能正常运行。这期间修改了很多原先没有考虑的东西,感觉自己进步了很多,对系统试图的运用熟练不少!庆祝一下!

今天重新整理了一下,希望能够对同样有类似需求的人有帮助,也希望能够得到一些宝贵意见,谢谢!

注意:如果想将两个数据库里面所以的表都改成一样的,那就自己去写个循环来调用吧。

评论 7
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值