修改指定表中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