全局修改数据库字段类型

create   PROC  p_typeTotype
@type   tinyint = 0 ,   -- 修改方式，0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列，报告不可修改列
@typefrom   nvarchar ( 50 ),
@typeto   nvarchar ( 50 )
AS
SET  NOCOUNT  ON
-- 查询非unicode列转换为unicode列的可行性
SELECT  TableName = o.name,FieldName = c.name,
    CurrentFieldType = t.name + N ' ( ' + CAST (c.prec  as   varchar ) + N ' ) '
         + CASE   WHEN  c.isnullable = 1   THEN  N ''   ELSE  N '  NOT '   END
         + N '  NULL ' ,
    NoChangeCause = CAST ( STUFF (
         CASE   WHEN   COLUMNPROPERTY (c.id,c.name,N ' IsComputed ' ) = 1
             THEN  N ' ,计算列 '   ELSE  N ''   END
         + CASE   WHEN  c.cdefault = 0   THEN  N ''   ELSE  N ' ,列具有默认值 '   END
         + CASE   WHEN   EXISTS (
                 SELECT   *   FROM  sysindexkeys idxk,sysindexes idx
                 WHERE  idxk.id = c.id 
                     AND  idxk.colid = c.colid
                     AND  idxk.id = idx.id
                     AND  idxk.indid = idx.indid
                     AND  idx.indid  NOT   IN ( 0 , 255 )
                     AND   INDEXPROPERTY (idx.id,idx.name,N ' IsAutoStatistics ' ) = 0 )
             THEN  N ' ,列被主键、唯一键、索引、STATISTICS引用 '   ELSE  N ''   END
         + CASE   WHEN   EXISTS (
                 SELECT   *   FROM  sysforeignkeys  WHERE  fkeyid = c.id  AND  fkey = c.colid)
             THEN  N ' ,列被外键约束引用 '   ELSE  N ''   END
         + CASE   WHEN   EXISTS (
                 SELECT   *   FROM  sysobjects oc,sysdepends d
                 WHERE  oc.parent_obj = o.id
                     AND   OBJECTPROPERTY (oc.id,N ' IsCheckCnst ' ) = 1
                     AND  d.id = oc.id
                     AND  d.depnumber = c.colid)
             THEN  N ' ,列被CHECK约束引用 '   ELSE  N ''   END , 1 , 1 ,N '' )  as   nvarchar ( 4000 ))
INTO  #  FROM  sysobjects o,syscolumns c,systypes t
WHERE  o.id = c.id  and  o.status >= 0
     AND   OBJECTPROPERTY (o.id,N ' IsUserTable ' ) = 1
     AND  t.xusertype = c.xusertype
     AND  t.name  in ( @typefrom )
IF   @@ROWCOUNT = 0   RETURN

-- 如果需要，修改非unicode列为unicode列
IF   @type = 2   OR   NOT   EXISTS ( SELECT   *   FROM  #  WHERE  NoChangeCause > '' )
BEGIN
     SET  XACT_ABORT  ON
     BEGIN   TRAN
         DECLARE  tb  CURSOR  LOCAL
         FOR
         SELECT  N ' ALTER TABLE  ' + QUOTENAME (TableName)
             + N '  ALTER COLUMN  ' + QUOTENAME (FieldName)
             +   @typeto
         FROM  #
         WHERE  NoChangeCause  IS   NULL
         DECLARE   @sql   nvarchar ( 4000 )
         OPEN  tb
         FETCH  tb  INTO   @sql
         WHILE   @@FETCH_STATUS = 0
         BEGIN
             EXEC  sp_executesql  @sql
             FETCH  tb  INTO   @sql
         END
         CLOSE  tb
         DEALLOCATE  tb
     COMMIT   TRAN
END

-- 显示不能修改的列
SELECT  TableName,FieldName,CurrentFieldType,
    NoChangeCause = ISNULL (NoChangeCause,N ' 可以修改(或者已经修改成功) ' )
FROM  #
ORDER   BY   CASE   WHEN  NoChangeCause  IS   NULL   THEN   1   ELSE   0   END ,TableName

GO

转载于:https://www.cnblogs.com/kasafuma/archive/2007/12/29/1019388.html