批量转换字段类型 - SQL Server论坛

第 2 楼

得分:0

最好你先把WHILE注释掉自己测下,另外,外键问题可能导致修改不到,循环个寂寞,基本上是这思路

程序代码：

DECLARE @t TABLE( rank1 INT, 表名 VARCHAR(50), 字段名 VARCHAR(50), 数据类型 VARCHAR(20))
INSERT @t
    SELECT ROW_NUMBER() OVER ( ORDER BY S2.colid ), S.name 表名, S2.name 字段名, S3.name 数据类型
        FROM sys.sysobjects AS S
             JOIN sys.syscolumns AS S2 ON S2.id = S.id
             JOIN sys.systypes AS S3 ON S3.xtype = S2.xtype
        WHERE S.type = 'u'
              AND S3.name = 'varchar'

SELECT * FROM @t AS T

DECLARE @tLen INT, @i INT = 1, @sql VARCHAR(100)
SELECT @tLen = SUM(1) FROM @t AS T
SELECT @tLen

WHILE( @i <= @tLen )
BEGIN
    SELECT 'rank1:' + CONVERT(VARCHAR(20), T.rank1) + ' 修改表:' + T.表名 + ' 字段:' + T.字段名
        FROM @t AS T
        WHERE T.rank1 = @i
    SELECT @sql = 'ALTER TABLE ' + T.表名 + ' ALTER COLUMN ' + T.字段名 + ' NVARCHAR(100)'
        FROM @t AS T
        WHERE T.rank1 = @i
    EXEC( @sql )
    SET @i += 1
END