DECLARE DataClean CURSOR
FOR
SELECT a.name AS t_name ,
b.name AS c_name ,
c.DATA_TYPE
FROM sys.tables a
JOIN sys.columns b ON b.object_id = a.object_id
JOIN INFORMATION_SCHEMA.COLUMNS c ON b.name = c.COLUMN_NAME
AND a.name = c.TABLE_NAME
OPEN DataClean
DECLARE @t_name VARCHAR(100) ,
@c_name VARCHAR(100) ,
@DATA_TYPE VARCHAR(100)
FETCH NEXT FROM DataClean INTO @t_name, @c_name, @DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ('ALTER TABLE ' + @t_name + ' ALTER COLUMN ' + @c_name+ ' NVARCHAR(2500)')
EXEC ('ALTER TABLE '+@t_name+ ' ALTER COLUMN '+ @c_name+' NVARCHAR(2500)')
PRINT ( '表:' + @t_name + '--字段:' + @c_name + '--修改成功' )
FETCH NEXT FROM DataClean INTO @t_name, @c_name, @DATA_TYPE
END
--关闭游标
CLOSE DataClean
--释放资源
DEALLOCATE DataClean