批量变更字段类型

最新推荐文章于 2024-04-25 23:53:15 发布

夏荷雨声

最新推荐文章于 2024-04-25 23:53:15 发布

阅读量600

点赞数

本文链接：https://blog.youkuaiyun.com/zdsyk/article/details/8504712

版权

DECLARE ChangeField CURSOR
FOR
    SELECT a.Name AS '表名' ,
            b.Name AS '列名' ,
            c.Name AS '类型' ,
            b.max_length AS '字节数'
        --b.Precision AS '整数位' ,
        --b.Scale AS '小数位'
    FROM    Sys.tables a
            INNER JOIN Sys.columns b ON a.object_id = b.object_id
            INNER JOIN sys.Types c ON c.User_Type_ID = b.User_Type_ID
    WHERE   c.name = 'char'
            OR c.name = 'varchar' ORDER BY a.Name
OPEN ChangeField
DECLARE @TableName VARCHAR(200) ,
    @FieldName VARCHAR(200) ,
    @TypeName VARCHAR(50) ,
    @FieldLen INT ,
    @SQL VARCHAR(8000)
FETCH NEXT FROM ChangeField INTO @TableName, @FieldName, @TypeName, @FieldLen
WHILE ( @@fetch_status = 0 )
    BEGIN
        SET @SQL = ''
        IF @TypeName = 'char'
            BEGIN
                SET @sql = 'ALTER TABLE ' + @TableName
                    + ' NOCHECK CONSTRAINT ALL '
                SET @sql = @sql + ' ALTER TABLE ' + @TableName
                    + ' ALTER COLUMN ' + @FieldName + ' nchar('
                    + CONVERT(VARCHAR(20), @FieldLen) + ')'
                SET @sql = @sql + ' ALTER TABLE ' + @TableName
                    + ' CHECK CONSTRAINT ALL '
            END
        ELSE
            BEGIN
                SET @sql = 'ALTER TABLE ' + @TableName
                    + ' NOCHECK CONSTRAINT ALL '
                SET @sql = @sql + 'ALTER TABLE ' + @TableName
                    + ' ALTER COLUMN ' + @FieldName + ' nvarchar('
                    + CONVERT(VARCHAR(20), @FieldLen) + ')'
                SET @sql = @sql + ' ALTER TABLE ' + @TableName
                    + ' CHECK CONSTRAINT ALL '
            END
        PRINT @sql
        --EXEC(@sql)
        FETCH NEXT FROM ChangeField INTO @TableName, @FieldName, @TypeName,
            @FieldLen
    END
CLOSE ChangeField
DEALLOCATE ChangeField