把数据库表中所有varchar类型的字段修改成nvarchar
不多说,直接上脚本。
DECLARE @allUserTables TABLE
(
Id INT IDENTITY(1,1) NOT NULL,
tableName VARCHAR(200),
tableId INT
)
INSERT INTO @allUserTables ( tableName,tableId )
SELECT name, object_id FROM sys.objects WHERE type='U' AND name <> 'dtproperties' AND name <> 'sysdiagrams'
DECLARE @totalT INT
DECLARE @count INT = 1
DECLARE @tableId INT
DECLARE @tableName VARCHAR(200)
DECLARE @xtypeVarchar TINYINT
SELECT @totalT = COUNT(1) FROM @allUserTables
SELECT @xtypeVarchar = xtype FROM systypes WHERE name='varchar'
WHILE @count <= @totalT
BEGIN
SELECT @tableId = tableId, @tableName = tableName FROM @allUserTables WHERE id = @count
DECLARE @tableCurrent TABLE (
Id INT IDENTITY(1,1),
columnName VARCHAR(200),
columnLength int )
INSERT INTO @tableCurrent (columnName, columnLength)
SELECT name,[length] FROM sys.syscolumns WHERE ID = @tableId AND xtype = @xtypeVarchar
DECLARE @totalT2 INT
DECLARE @count2 INT = 1
DECLARE @columnName VARCHAR(200)
DECLARE @columnLength INT
SELECT @totalT2 = COUNT(1) FROM @tableCurrent
WHILE @count2 <= @totalT2
BEGIN
SELECT @columnName = columnName, @columnLength = columnLength FROM @tableCurrent
exec('alter table ' + @tableName + ' alter Column ' + @columnName + ' NvarChar(' + @columnLength + ')')
set @count2 = @count2 + 1
END
SET @count = @count + 1
END