修改指定表中varchar類型為nvarchar, text為ntext存儲過程

CREATE   PROC  SP_CHANGE_TABLE_CHARTYPE
(
     @tableName      nvarchar ( 128 )             -- 表名
)
AS
DECLARE   @varcharTypeId          tinyint              -- varchar數據類型編號
DECLARE   @textTypeId              tinyint              -- text數據類型編號
DECLARE   @ColumnName              nvarchar ( 128 )     -- 系統列名
DECLARE   @CType                  VARCHAR ( 1 )         -- 類型
DECLARE   @IsNullAble              char ( 1 )             -- 是否允許為空
DECLARE   @Length                  int                  -- 欄位長度
DECLARE   @ColumnType              int                  -- 列類型
DECLARE   @DCName                  nvarchar ( 128 )     -- 默認值約束名稱
DECLARE   @DCValue              nvarchar ( max )     -- 默認值約束表達式
DECLARE   @CCName                  nvarchar ( 128 )     -- 自定義約束名稱
DECLARE   @CCValue              nvarchar ( max )     -- 自定義約束表達式
DECLARE   @objectid              int
DECLARE   @sqlstr                  nvarchar ( max )

IF   NOT   EXISTS ( SELECT   *   FROM  sys.tables  WHERE  name = @tableName )
BEGIN
     RAISERROR ( ' 數據表不存在，請確認表名是否正確！ ' ,  16 ,  1 )
     RETURN
END
SET   @objectid = OBJECT_ID ( @tableName )

-- 取得varchar, text類別編碼，名稱不能重複，可通過名稱查詢【已測試過】
SELECT   @varcharTypeId = system_type_id  FROM  sys.types  WHERE  name = ' varchar '
SELECT   @textTypeId = system_type_id  FROM  sys.types  WHERE  name = ' text '

-- 取得主鍵列名
SELECT      COL_NAME (IC. object_id , IC.column_id)  AS  ColumnName
INTO     #PrimaryColumn
FROM     sys.index_columns  AS  IC
LEFT   JOIN  sys.indexes  AS  I  ON  I. object_id = IC. object_id   AND  I.index_id = IC.index_id
WHERE     I.is_primary_key = 1
     AND  IC. object_id = @objectid

-- 更新類型
DECLARE  cur_columns_varchar  CURSOR  STATIC  FOR
     -- 查找非主鍵varchar, text類型字段並找出相應默認值及約束
     SELECT     C.name  AS  ColumnName, C.is_nullable, D.name  AS  DCName, D.definition  AS  DCValue,
            CK.name  AS  CCName, CK.definition  AS  CCValue, C.max_length, C.system_type_id
     FROM     sys.columns  AS  C                                                                                         -- 系統字段視圖
     LEFT   JOIN  sys.default_constraints  AS  D  ON  D.parent_object_id = C. object_id   AND  D.parent_column_id = C.column_id         -- 默認值視圖
     LEFT   JOIN  sys.check_constraints  AS  CK  ON  CK.parent_object_id = C. object_id   AND  CK.parent_column_id = C.column_id     -- 約束視圖
     WHERE     C. object_id = @objectid
         AND  (C.system_type_id = @varcharTypeId   OR  C.system_type_id = @textTypeId )
         AND   NOT   EXISTS ( SELECT   *   FROM  #PrimaryColumn  WHERE  ColumnName = C.name)
OPEN  cur_columns_varchar
FETCH   next   FROM  cur_columns_varchar  INTO  
             @ColumnName ,  @IsNullAble ,  @DCName ,  @DCValue ,
             @CCName ,  @CCValue ,  @Length ,  @ColumnType
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
     SET   @sqlstr = ''
     -- 默認值約束不為空
     IF   @DCName   IS   NOT   NULL
         SET   @sqlstr = ' ALTER TABLE  ' + @tableName + '  DROP CONSTRAINT  ' + @DCName + ' ; '
    
     -- 自定義約束不為空
     IF   @CCName   IS   NOT   NULL
         SET   @sqlstr = @sqlstr + ' ALTER TABLE  ' + @tableName + '  DROP CONSTRAINT  ' + @CCName + ' ; '
    
     -- 更改數據類型
     IF   @ColumnType = @varcharTypeId          -- varchar
     BEGIN
         SET   @sqlstr = @sqlstr + ' ALTER TABLE  ' + @tableName + '  ALTER COLUMN  ' + @ColumnName + '  NVARCHAR( ' + CAST ( @Length   AS   VARCHAR ) + ' ) '
         IF   @IsNullAble = 0
         BEGIN
             SET   @sqlstr = @sqlstr + '  NOT NULL '
         END
        
     END
     ELSE                                  -- text
     BEGIN
         SET   @sqlstr = @sqlstr + ' ALTER TABLE  ' + @tableName + '  ADD TMP__ ' + @ColumnName + '  ntext; '
         EXEC ( @sqlstr )
        
         -- +CHAR(10)+'GO'+CHAR(10)
         SET   @sqlstr = ' UPDATE  ' + @tableName + '  SET TMP__ ' + @ColumnName + ' = ' + @ColumnName
                     + ' ;ALTER TABLE  ' + @tableName + '  DROP COLUMN  ' + @ColumnName
                     + ' ;EXEC sp_rename  ' + CHAR ( 34 ) + @tableName + ' .TMP__ ' + @ColumnName + CHAR ( 34 ) + ' ,  ' + CHAR ( 34 ) + @ColumnName + CHAR ( 34 ) + ' ,  ' + CHAR ( 34 ) + ' COLUMN ' + CHAR ( 34 )
     END
     SET   @sqlstr = @sqlstr + ' ; '
    
     -- 默認值約束不為空
     IF   @DCName   IS   NOT   NULL
         SET   @sqlstr = @sqlstr + ' ALTER TABLE  ' + @tableName + '  ADD CONSTRAINT  ' + @DCName + '  DEFAULT  ' + @DCValue + '  FOR  ' + @ColumnName + ' ; '
    
     -- 自定義約束不為空
     IF   @CCName   IS   NOT   NULL
         SET   @sqlstr = @sqlstr + ' ALTER TABLE  ' + @tableName + '  ADD CONSTRAINT  ' + @CCName + '  CHECK ( ' + @CCValue + ' ); '
    
     -- 執行
     -- SELECT @sqlstr
     EXEC ( @sqlstr )
     FETCH   next   FROM  cur_columns_varchar  INTO  
             @ColumnName ,  @IsNullAble ,  @DCName ,  @DCValue ,
             @CCName ,  @CCValue ,  @Length ,  @ColumnType
END
CLOSE  cur_columns_varchar
DEALLOCATE  cur_columns_varchar

DROP   TABLE  #PrimaryColumn

转载于:https://www.cnblogs.com/ywkpl/archive/2011/05/13/2045523.html