Sql Server 2005 使用代码修改列的属性(存储过程)

最近很是郁闷，总公司要求把所有分公司的DB统一管理，以前都是各顾各的，什么添加字段修改数据类型都各做各的。现在要求统一很是麻烦，如果其中一个DB的某字段修改那么其他所有DB里面只要有这个表的都要改。

唉！以前很轻松的只要按需求点点鼠标就改一个DB就好的事情，现在要点60多个数据库去完成操作，而且还有很多DB不在一个SERVER上面，想我高升一向都是很懒的人，这种事情肯定是不愿意做的，可以这种事情低级DBA不做你要谁去做啊，于是一恨心放弃了一个宝贵的双休写了一个统一修改列属性的存储过程，使用代码更改列的数据类型，不知道算不算是远程修改了。以下示例将 AdventureWorks 数据的 Employee 表 Title 列的长度改成55。

ALTER   TABLE  HumanResources.Employee
ALTER   COLUMN  Title  nvarchar ( 55 )

ALTER TABLE  通过更改、添加或删除列和约束，重新分配分区，或者启用或禁用约束和触发器，从而修改表的定义。修改一个表的定义这个语法每个初学数据库的人都是知道的，不过很多情况下我们并不能直接修改，比如以下几种情况：

• 用于索引的列。
• 用于 CHECK、FOREIGN KEY、UNIQUE 或 PRIMARY KEY 约束的列。
• 与默认值（由 DEFAULT 关键字定义）相关联的列，或绑定到默认对象的列。
• 绑定到规则的列。

在实际应用中，如果我们要修改的某一列上面建了索引或者建有约束的话，那个这条语句很可能是要报错的，当然这个和数据类型也有关系，具体的原理是什么我也不是很清楚。可以试一下将 Title 列加上一个索引，然后修改一下试试看。

-- 将 Title 列加上索引，这个只是为了测试一下加了索引后还能不能修改这一列的属性
CREATE   NONCLUSTERED   INDEX  IX_Employee  ON  HumanResources.Employee
 (
   Title
 )  WITH ( STATISTICS_NORECOMPUTE  =   OFF , IGNORE_DUP_KEY  =   OFF , ALLOW_ROW_LOCKS  =   ON , ALLOW_PAGE_LOCKS  =   ON )  ON   [ PRIMARY ]
GO

--加上索引后，还是刚才的修改语句，这里是60因为上一条已经改成55了

ALTER   TABLE  HumanResources.Employee
ALTER   COLUMN  Title  nvarchar (60 )

GO

现在执行这句话不出意外应该抱错了，如果上面有约束也是一样的会报错，不过这和数据类型有关，具体有哪些情况下会出现我也需要有人帮我解惑。

写点基本语法，多温习一下基础知识还是必要的，添加和删除默认约束，因为有时候用代码去修改列的时候有默认约束也是不能修改的，所以要删了重新建。CHECK 约束的建法差不多的不想写了。

--给 Title 列添加一个默认约束 '' 约束名 DF_Employee_Title

ALTER   TABLE  HumanResources.Employee  ADD   CONSTRAINT
DF_Employee_Title  DEFAULT   ''   FOR  Title

--删除 Title 上的默认约束 DF_Employee_Title
ALTER   TABLE  HumanResources.Employee
DROP   CONSTRAINT  DF_Employee_Title

由于要修改的列可能已经加上了索引或者约束，这就为我现在使用代码修改列的属性带来了一定的麻烦，所以我才花两天时间写这个存储过程，其实关键代码就只有几句。我建到了msdb数据库下面，还加了密。

USE  msdb
GO
-- 建立人：  高升
-- 建立日期：2007/06/16
-- 修改日期：
-- 功能目的：主要就是修改列的属性，不过是可以执行以后修改N多DB的，算不算远程修改列？
-- 注意：    就我可以用，因为用的临时表太多了，汗！
CREATE   PROCEDURE  dbo.ModifyColumn
   @dbName  sysname  =   '' ,         -- 要修改的数据库名，不写就是所以的数据库
   @tableName  sysname  =   '' ,     -- 要修改的表
   @columnName  sysname  =   '' ,     -- 要修改的列
   @type   varchar ( 2 ),             -- M修改A新增
   @dataType   nvarchar ( 128 )  =   '' ,     -- 数据类型
   @constraint   varchar ( 100 )  =   '' ,     -- 约束名，新增的时候写
   @is_exec   bit   =   0 ,             -- 是打印是生成的代码还是直接执行
   @custom   varchar ( max )  =   ''
WITH  ENCRYPTION
AS

IF  ( SUSER_SNAME ()  !=   ' GaoS ' )     -- 我的登陆名，sa都不能执行我的东西
   RAISERROR ( ' The user does not have permission to perform this action. ' , 16 , 1 )

DECLARE   @exec   varchar ( max )     -- 存放执行的语句
DECLARE   @name  sysname         -- 当前要执行的数据库名
DECLARE   @server_id   varchar ( 4 )     -- 那个Server
DECLARE   @exec1   varchar ( 100 )     -- 临时执行的语句
DECLARE   @exec2   varchar ( 100 )     --
DECLARE   @exec3   varchar ( 300 )     --
DECLARE   @constraintName   varchar ( 100 )     -- 默认约束的约束名
DECLARE   @definition   nvarchar ( 50 )         -- 默认值
DECLARE   @sno   smallInt          -- 用于循环

IF  ( @is_exec   <>   1 )  SET   @is_exec   =   0      -- 该值只能是0或1

IF   exists  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##myDB ' )
BEGIN
   PRINT  ( ' There is already an object named  '' ModifyColumn ''  in the database. ' )
   RETURN ;
END

--这个表放DB的名字，以及DB所在的SERVER

CREATE   TABLE  ##myDB(sno  smallInt   identity ( 1 , 1 ),name sysname,server_id  tinyInt )

IF  ( @dbName   =   '' )     -- 查询所有要执行的DB,除去系统和临时DB
BEGIN
   INSERT  ##myDB  select  name, 1   FROM  master.sys.databases
    WHERE  database_id  >   4   and  name  not   like   ' %temp% '   ORDER   BY  name

   INSERT  ##myDB  select  name, 2   FROM  SHDB2.master.sys.databases
    WHERE  database_id  >   4   and  name  not   like   ' %temp% '   ORDER   BY  name

   INSERT  ##myDB  select  name, 3   FROM  TWDB.master.sys.databases
    WHERE  database_id  >   4   and  name  not   like   ' %temp% '   ORDER   BY  name

   INSERT  ##myDB  select  name, 4   FROM  GZDB.master.sys.databases
    WHERE  database_id  >   4   and  name  not   like   ' %temp% '   ORDER   BY  name
END
ELSE      -- 如果指定了要修改的数据库名
BEGIN
   DECLARE   @getDBName   varchar ( 500 )
   SET   @getDBName   =   ' INSERT ##myDB select name,1 FROM master.sys.databases
        WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
   EXEC  ( @getDBName )
   SET   @getDBName   =   ' INSERT ##myDB select name,2 FROM SHDB2.master.sys.databases
        WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
   EXEC  ( @getDBName )
   SET   @getDBName   =   ' INSERT ##myDB select name,3 FROM TWDB.master.sys.databases
        WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
   EXEC  ( @getDBName )
   SET   @getDBName   =   ' INSERT ##myDB select name,4 FROM GZDB.master.sys.databases
        WHERE name in ( ''' + @dbName + ''' ) ORDER BY name '
   EXEC  ( @getDBName )
END

SET   @sno   =   1
WHILE  ( @sno   <=  ( SELECT   COUNT (sno)  FROM  ##MyDB))
   BEGIN          -- 终于可以开始了，一个DB一个DB的循环吧，不喜欢用游标

   SELECT   @name   =  name, @server_id   =  server_id  FROM  ##MyDB  WHERE  sno  =   @sno

IF  ( @server_id   =   2 )     -- 如果是本地的服务器2
BEGIN
   EXEC  SHDB2.msdb.dbo.ModifyColumn --其他Server上的这个存储过程比这个简单多了
                 @dbName   =   @name ,
                 @type   =   @type ,
                 @tableName   =   @tableName ,
                 @columnName   =   @columnName ,
                 @dataType   =   @dataType ,
                 @constraint   =   @constraint ,
                 @is_exec   =   @is_exec ,
                 @custom   =   @custom
   SET   @exec   =   ''
END

ELSE   IF  ( @server_id   =   3 )     -- 如果是台湾的服务器
BEGIN
   EXEC  TWDB.msdb.dbo.ModifyColumn
                 @dbName   =   @name ,
                 @type   =   @type ,
                 @tableName   =   @tableName ,
                 @columnName   =   @columnName ,
                 @dataType   =   @dataType ,
                 @constraint   =   @constraint ,
                 @is_exec   =   @is_exec ,
                 @custom   =   @custom
   SET   @exec   =   ''
END

ELSE   IF  ( @server_id   =   4 )     -- 如果是广州的服务器
BEGIN
   EXEC  GZDB.msdb.dbo.ModifyColumn
                 @dbName   =   @name ,
                 @type   =   @type ,
                 @tableName   =   @tableName ,
                 @columnName   =   @columnName ,
                 @dataType   =   @dataType ,
                 @constraint   =   @constraint ,
                 @is_exec   =   @is_exec ,
                 @custom   =   @custom
   SET   @exec   =   ''
END

ELSE   IF  ( @type   =   ' M ' )     -- 修改某个字段，这里才是重点
BEGIN
   DECLARE   @exec4   varchar ( 500 )     -- 删除索引或键
   DECLARE   @exec5   varchar ( 500 )     -- 添加索引或键
   DECLARE   @exec6   varchar ( 500 )     -- 临时用用为了得到几个参数
   DECLARE   @i_name  sysname         -- 索引名
   DECLARE   @is_key   bit              -- 是否主键
   DECLARE   @i_no   tinyInt               -- 索引的序号
   DECLARE   @c_name   varchar ( 100 )     -- 索引所在的列名
   DECLARE   @i_type   varchar ( 60 )     -- 是否聚集
   DECLARE   @is_unique   varchar ( 6 )     -- 是否唯一

   SET   @exec4   =   ''
   SET   @exec5   =   ''
   SET   @i_name   =   ''

   IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##Temp_dafalit ' )
     DROP   TABLE  ##Temp_dafalit     -- 放默认值的
   IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##temp_indexName ' )
     DROP   TABLE  ##temp_indexName     -- 放索引名的

   SET   @constraintName   =   ''      -- 约束名
   SET   @definition   =   ''          -- 默认值
   SET   @exec3   =   ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +
         ' SELECT name,definition INTO ##Temp_dafalit FROM sys.default_constraints
          WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND name =  '''   +   @columnName   +   ''' ) '
   EXEC  ( @exec3 )
   SELECT   @constraintName   =  name, @definition   =  definition  FROM  ##Temp_dafalit

   IF  ( @constraintName   !=   '' )     -- 如果该列有默认约束。修改前先删除，改好后还原。
   BEGIN
     SET   @exec1   =   ' ALTER TABLE  '   +   @tableName   +   '  DROP CONSTRAINT  '   +   @constraintName   +   char ( 13 )
     SET   @exec2   =   ' ALTER TABLE  '   +   @tableName   +   '  ADD  CONSTRAINT  '   +   @constraintName   +   '  DEFAULT  '   +   @definition   +   '  FOR  '   +   @columnName   +   char ( 13 )
   END
   ELSE
   BEGIN
     SET   @exec1   =   ''
     SET   @exec2   =   ''
   END

   SET   @exec6   =    ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +
     ' SELECT IDENTITY(tinyInt,1,1)i_no,c.name,c.is_primary_key INTO ##temp_indexName
       FROM sys.columns a inner join sys.index_columns b 
    ON a.object_id = b.object_id AND a.column_id = b.column_id inner join sys.indexes c
    ON b.object_id = c.object_id AND b.index_id = c.index_id
    WHERE a.object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND a.name =  '''   +   @columnName   +   ''''
   EXEC  ( @exec6 )

   SET   @i_no   =   1   -- 此处的循环很无奈，因为有的列上可能有两个索引，也不知道设计有没问题
   WHILE  ( @i_no   <=  ( SELECT   COUNT (i_no)  FROM  ##temp_indexName))
   BEGIN
     SELECT   @i_name   =  name , @is_key   =  is_primary_key  FROM  ##temp_indexName  WHERE  i_no  =   @i_no

     IF  ( @i_name   !=   '' )     -- 如果该列有索引。修改前先删除，改好后还原。
     BEGIN
       IF  ( @is_key   =   0 )     -- 判断是否主键，主键的索引和非主键索引删除方法不一样
         SET   @exec4   =   @exec4   +   ' DROP INDEX [ '   +   @i_name   +   ' ] ON [ '   +   @tableName   +   ' ] WITH ( ONLINE = OFF ) '   +   char ( 13 )
       ELSE
         SET   @exec4   =   @exec4   +   ' ALTER TABLE [ '   +   @tableName   +   ' ] DROP CONSTRAINT [ '   +   @i_name   +   ' ] '   +   char ( 13 )

       IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##temp_indexinfo ' )
         DROP   TABLE  ##temp_indexinfo     -- 存放临时的索引信息

       SET   @exec6   =    ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +
         ' SELECT * INTO ##temp_indexinfo FROM
        (SELECT a.name,b.is_descending_key,c.type_desc, c.is_unique,is_primary_key
        FROM sys.columns a inner join sys.index_columns b 
        ON a.object_id = b.object_id AND a.column_id = b.column_id inner join sys.indexes c
        ON b.object_id = c.object_id AND b.index_id = c.index_id
        WHERE a.object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND c.name =  '''   +   @i_name   +   ''' )a '
       EXEC  ( @exec6 )
       SET   @c_name   =   ''
       SELECT   @i_type   =  type_desc,  @is_unique   =  ( CASE  is_unique  WHEN   1   THEN   ' UNIQUE '   ELSE   ''   END ), @is_key   =  is_primary_key  FROM  ##temp_indexinfo
       SELECT   @c_name   =   @c_name   +   ' ,[ '   +  name  +  ( CASE  is_descending_key  WHEN   1   THEN   ' ] DESC '   ELSE   ' ] ASC '   END )  FROM  ##temp_indexinfo

       IF  ( @is_key   =   0 )     -- 主键的索引和非主键索引创建方法不一样
         SET   @exec5   =   @exec5   +   ' CREATE  '   +   @is_unique   +   '   '   +   @i_type   +   '  INDEX [ '   +   @i_name   +   ' ] ON [ '   +   @tableName   +   ' ] ( '   +   SUBSTRING ( @c_name , 2 , len ( @c_name ))  +   ' ) '   +   char ( 13 )
       ELSE
         SET   @exec5   =   @exec5   +   ' ALTER TABLE [ '   +   @tableName   +   ' ] ADD CONSTRAINT [ '   +   @i_name   +   ' ] PRIMARY KEY  '   +   @i_type   +   '  ( '   +   SUBSTRING ( @c_name , 2 , len ( @c_name ))  +   ' ) '   +   +   char ( 13 )
     END
     ELSE
     BEGIN
       SET   @exec4   =   ''
       SET   @exec5   =   ''
     END
     SET   @i_no   =   @i_no   +   1      -- 下一个索引
   END      -- while

   SET   @exec   =  
     ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND type =  '' U '' )BEGIN '   +   char ( 13 )  +
         ' IF ((SELECT is_nullable FROM sys.columns WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND name =  '''   +   @columnName   +   ''' ) = 0) '   +   char ( 13 )  +
         ' ALTER TABLE  '   +   @tableName   +   '  ALTER COLUMN  '   +   @columnName   +   '   '   + @dataType   +   '  not null  '   +   @constraint   +   char ( 13 )  +     
         ' ELSE ALTER TABLE  '   +   @tableName   +   '  ALTER COLUMN  '   +   @columnName   +   '   '   + @dataType   +   '   '   +   @constraint   +   char ( 13 )  +
     ' PRINT  '''   +   @name   +   '  的  '   +   @tableName   +   '  表修改  '   +   @columnName   +   '  列成功 ''  END '   +   char ( 13 )  +
     ' ELSE PRINT  '''   +   @name   +   '  中没有  '   +   @tableName   +   '  表 '''   +   char ( 13 )

   SET   @exec   =   ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +   @exec1   +   @exec4   +   @exec   +   @exec2   +   @exec5

   IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##Temp_dafalit ' )
     DROP   TABLE  ##Temp_dafalit
   IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##temp_indexName ' )
     DROP   TABLE  ##temp_indexName
   IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##temp_indexinfo ' )
     DROP   TABLE  ##temp_indexinfo
   -- 居然用了3个临时表，还好这个不需要考虑效率问题
END

ELSE   IF  ( @type   =   ' A ' )     -- 增加某个字段
BEGIN
   SET   @exec   =   ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +  
     ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND type =  '' U '' ) '   +   char ( 13 )  +
         ' BEGIN ALTER TABLE  '   +   @tableName   +   '  ADD  '   +   @columnName   +   '   '   + @dataType   +   '   '   +   @constraint   +   char ( 13 )  +
         ' PRINT  '''   +   @name   +   '  的  '   +   @tableName   +   '  表增加  '   +   @columnName   +   '  列成功 ''  END '   +   char ( 13 )  +
     ' ELSE PRINT  '''   +   @name   +   '  中没有  '   +   @tableName   +   '  表 '''   +   char ( 13 )
END

ELSE   IF  ( @type   =   ' D ' )     -- 为列添加默认值
BEGIN
   SET   @exec   =  
     ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND type =  '' U '' )BEGIN '   +   char ( 13 )  +
         ' ALTER TABLE  '   +   @tableName   +   '  ADD CONSTRAINT DF_ '   +   @tableName   +   ' _ '   +   @columnName   + '  DEFAULT ( '   +   @constraint   +   ' ) FOR  '   +   @columnName   +   char ( 13 )  +  
         ' PRINT  '''   +   @name   +   '  的  '   +   @tableName   +   '  表为  '   +   @columnName   +   '  列添加默认值成功 ''  END '   +   char ( 13 )  +
     ' ELSE PRINT  '''   +   @name   +   '  中没有  '   +   @tableName   +   '  表 '''   +   char ( 13 )

   IF   exists  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##Temp_dafalit ' )
     DROP   TABLE  ##Temp_dafalit
   SET   @constraintName   =   ''  
   SET   @definition   =   ''
   SET   @exec3   =   ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +
         ' SELECT name,definition INTO ##Temp_dafalit FROM sys.default_constraints
          WHERE object_id = (SELECT default_object_id FROM sys.columns WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND name =  '''   +   @columnName   +   ''' ) '
   EXEC  ( @exec3 )
   SELECT   @constraintName   =  name, @definition   =  definition  FROM  ##Temp_dafalit

   IF  ( @constraintName   !=   '' )
   BEGIN
     SET   @exec1   =    ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +   ' alter table  '   +   @tableName   +   '  drop constraint  '   +   @constraintName
     SET   @exec   =   @exec1   +   char ( 13 )  +   @exec   +   char ( 13 )
   END
   ELSE
     SET   @exec   =   ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +   @exec

   IF   EXISTS  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##Temp_dafalit ' )
     DROP   TABLE  ##Temp_dafalit
END

ELSE   IF  ( @type   =   ' Z ' )     -- 执行自定义动作
BEGIN
set   @exec   =   ' use  '   +   @name   +   ' ; '   +   char ( 13 )  +
     ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID( '''   +   @tableName   +   ''' ) AND type =  '' U '' )BEGIN '   +   char ( 13 )  +
         @custom   +   char ( 13 )  +   ' print  '' 在  '   +   @name   +   '  中执行成功 ''  END '   +   char ( 13 )  +
     ' ELSE  print  '' 在  '   +   @name   +   '  中没有这个表 '''   +   char ( 13 )
END

BEGIN  try     -- 开始执行
   IF  ( @is_exec   =   1 )  EXEC  ( @exec )
   ELSE   PRINT  ( @exec )
END  try
BEGIN  catch
   DECLARE   @error   nvarchar ( 500 )
   SET   @error   =   @name   +   '  的  '   +   @tableName   +   '  中失败，原因： '   +   char ( 13 )  +  ERROR_MESSAGE()
   RAISERROR ( @error , 16 , 1 )     -- 抛出错误信息    
END  catch

SET   @sno   =   @sno   +   1
END ;     -- 循环结束

IF   exists  ( SELECT   *   FROM  tempdb.sys.objects  WHERE  name  =   ' ##myDB ' )
   DROP   TABLE  ##myDB
GO

写的太复杂了，没办法服务器就要4个，以后可能会更多，暂时就60多个数据库，你说我不怎样写以后改某个字段的时候不是要累死吗？本来的设计就不是很好，增加字段修改数据类型是常有的事情。痛苦啊！

其实这个存储过程很简单，就是在修改某一列的时候先判断一下又没索引，有就先自动生成这个索引的删除和添加语句。然后是默认约束，也是一样有就生成删除的和添加的语句。CHECK约束我没写，一个是很复杂，另一个是我们的所有DB总共不到10个这样的约束，没那必要了。这些语句都生成完后，执行修改前先执行删除的，修改完了执行添加的，这样就一切OK了。

测试了没问题，以后看看能不能继续加点东西上去，还有我加密的主要原因是不想公司的那些高级DBA笑话，写的太幼稚了，效率不好，不过总算是能给我减轻工作压力了，高兴！

这篇Blog的地址：http://blog.youkuaiyun.com/hb_gx/archive/2007/06/18/1655990.aspx

我高升也只是个初学者，还是非常希望能得到网络上众多高手的指定的，欢迎各种批评！另外说句实话我这篇文章写的真的很烂！