批量转换字段类型

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