declare @rows int, @row int, @TabName varchar(128), --要改的表 @Column varchar(32), -- 要修改的列名 @Length int, --要修改的长度 @strSql varchar(512)declare @t table (Row int identity(1,1),Tab varchar(128),Col varchar(32),len int ) insert into @t select b.name as Tab,a.name as Col,a.max_length as Len from sys.columns a inner join sys.tables b on a.object_id = b.object_id inner join sys.types c on a.system_type_id = c.system_type_id and c.name <>'sysname' where c.name = 'varchar'set @rows = @@ROWCOUNT set @row = 1 while(@row <= @rows) begin select @TabName = Tab, @Column = Col, @Length = len from @t where Row = @row set @strSql = ' alter table '+ @TabName +' alter column '+ @Column +' nvarchar(' + convert(varchar(8),@Length)+')' print @strSql exec (@strSql) set @row = @row + 1 end
DECLARE @s VARCHAR(max) SELECT @s=ISNULL(@s+CHAR(10),'') +'ALTER TABLE ' +QUOTENAME(OBJECT_NAME(c.[object_id])) +' ALTER COLUMN '+QUOTENAME(c.NAME) +' NVARCHAR('+LTRIM(c.max_length*2)+')' FROM sys.columns c, sys.tables t WHERE t.[object_id]=c.[object_id] --print @s EXEC(@s) 这是没数据库的,varchar 改成 char 长度*2才能满足不出问题吧但是大于4000的乘2就会报错。请谨慎操作。
把一个条件不小心弄掉了 DECLARE @s VARCHAR(max) SELECT @s=ISNULL(@s+CHAR(10),'') +'ALTER TABLE ' +QUOTENAME(OBJECT_NAME(c.[object_id])) +' ALTER COLUMN '+QUOTENAME(c.NAME) +' NVARCHAR('+LTRIM(c.max_length*2)+')' FROM sys.columns c, sys.tables t WHERE t.[object_id]=c.[object_id] AND c.system_type_id=167 --print @s EXEC(@s)
你要对指定数据库中的每个表的每个字段进行修改,我只是把这些需要修改的字段拼接到了一起 然后执行你可以DECLARE @s VARCHAR(max) SELECT @s=ISNULL(@s+CHAR(10),'') +'ALTER TABLE ' +QUOTENAME(OBJECT_NAME(c.[object_id])) +' ALTER COLUMN '+QUOTENAME(c.NAME) +' NVARCHAR('+LTRIM(c.max_length*2)+')' FROM sys.columns c, sys.tables t WHERE t.[object_id]=c.[object_id] AND c.system_type_id=167 print @s把打印出来的语句 复制过来执行,也是一样的
IF OBJECT_ID('USP_BatchUpdateFieldType') Is Not Null Drop Procedure USP_BatchUpdateFieldType GOCreate Procedure USP_BatchUpdateFieldType(@OldType Varchar(30), @NewType Varchar(30)) As Begin Declare @Tbl Table (ObjName Varchar(128), FldName Varchar(128), TypeName Varchar(128), Length Int)
--获取对应的字段 Insert Into @Tbl Select C.NAME As ObjName, A.Name As FldName, B.Name As TypeName, A.Length From SYSCOLUMNS A Inner Join SYSTYPES B On A.xtype = B.xtype Inner Join SYSOBJECTS C ON A.ID = C.ID Where C.xtype = 'U' And B.Name = @OldType
Declare @ObjName Varchar(128), @FldName Varchar(128), @TypeName Varchar(128), @Length Int Declare tItem cursor for SELECT ObjName, FldName, TypeName, Length From @Tbl Open tItem FETCH NEXT FROM tItem into @ObjName, @FldName, @TypeName, @Length WHILE @@FETCH_STATUS = 0 BEGIN Exec ('Alter Table '+@ObjName+' Alter Column '+@FldName+' '+@NewType+'('+@Length+')') FETCH NEXT FROM tItem into @ObjName, @FldName, @TypeName, @Length END CLOSE tItem DEALLOCATE tItem End Go 写了个存储过程,这里没有考虑到当不带N的类型转带N类型的问题 比如: Varchar(5000) 转 NVarchar(5000) 这样的情况应该问出问题 给你个参考去修改吧
以下例子是针对修改单个库中的指定表将其varchar类型的字段类型改为nvarchar:
declare @num int,@tableName varchar(50),@columnName varchar(50), @typeName varchar(50),@max_length int,@str varchar(500) = ''declare cur_table cursor for with cte(num,tableName,columnName,typeName,max_length) as ( select row_number() over(order by ia.name asc) 'Num' , ia.name 'tableName',ib.name 'columnName', ic.name 'typeName',ib.max_length from test.sys.objects ia inner join test.sys.columns ib on ia.object_id = ib.object_id inner join test.sys.types ic on ib.user_type_id = ic.user_type_id where ia.type ='U' and ia.name = 'abc' ) select num,tableName,columnName,typeName,max_length from cte;open cur_table fetch next from cur_table into @num,@tableName, @columnName,@typeName,@max_length while(@@FETCH_STATUS = 0) begin if(@typeName = 'varchar') begin set @str = ' alter table '+@tableName+' alter column '+@columnName+' nvarchar('+str(@max_length)+') ' exec(@str); end fetch next from cur_table into @num,@tableName, @columnName,@typeName,@max_length end close cur_table; deallocate cur_table;
2、从表视图中得所有的表,判断哪个字段是varchar类型的
3、循环修改。
@row int,
@TabName varchar(128), --要改的表
@Column varchar(32), -- 要修改的列名
@Length int, --要修改的长度
@strSql varchar(512)declare @t table (Row int identity(1,1),Tab varchar(128),Col varchar(32),len int )
insert into @t
select b.name as Tab,a.name as Col,a.max_length as Len from sys.columns a inner join sys.tables b on a.object_id = b.object_id
inner join sys.types c on a.system_type_id = c.system_type_id and c.name <>'sysname'
where c.name = 'varchar'set @rows = @@ROWCOUNT
set @row = 1
while(@row <= @rows)
begin
select @TabName = Tab,
@Column = Col,
@Length = len
from @t where Row = @row set @strSql = ' alter table '+ @TabName
+' alter column '+ @Column +' nvarchar(' + convert(varchar(8),@Length)+')'
print @strSql
exec (@strSql)
set @row = @row + 1
end
SELECT @s=ISNULL(@s+CHAR(10),'')
+'ALTER TABLE '
+QUOTENAME(OBJECT_NAME(c.[object_id]))
+' ALTER COLUMN '+QUOTENAME(c.NAME)
+' NVARCHAR('+LTRIM(c.max_length*2)+')'
FROM sys.columns c,
sys.tables t WHERE t.[object_id]=c.[object_id]
--print @s
EXEC(@s)
这是没数据库的,varchar 改成 char 长度*2才能满足不出问题吧但是大于4000的乘2就会报错。请谨慎操作。
那为什么会出现,table了?应该只出现数据库的名字啊,而且我没看出来,那个是数据库的名字,我水平太差,而且object_id是什么,也不懂,关键是,那些该替换,我不知道啊
DECLARE @s VARCHAR(max)
SELECT @s=ISNULL(@s+CHAR(10),'')
+'ALTER TABLE '
+QUOTENAME(OBJECT_NAME(c.[object_id]))
+' ALTER COLUMN '+QUOTENAME(c.NAME)
+' NVARCHAR('+LTRIM(c.max_length*2)+')'
FROM sys.columns c,
sys.tables t WHERE t.[object_id]=c.[object_id]
AND c.system_type_id=167
--print @s
EXEC(@s)
然后执行你可以DECLARE @s VARCHAR(max)
SELECT @s=ISNULL(@s+CHAR(10),'')
+'ALTER TABLE '
+QUOTENAME(OBJECT_NAME(c.[object_id]))
+' ALTER COLUMN '+QUOTENAME(c.NAME)
+' NVARCHAR('+LTRIM(c.max_length*2)+')'
FROM sys.columns c,
sys.tables t WHERE t.[object_id]=c.[object_id]
AND c.system_type_id=167
print @s把打印出来的语句 复制过来执行,也是一样的
IF OBJECT_ID('USP_BatchUpdateFieldType') Is Not Null
Drop Procedure USP_BatchUpdateFieldType
GOCreate Procedure USP_BatchUpdateFieldType(@OldType Varchar(30), @NewType Varchar(30))
As
Begin
Declare @Tbl Table (ObjName Varchar(128), FldName Varchar(128), TypeName Varchar(128), Length Int)
--获取对应的字段
Insert Into @Tbl
Select C.NAME As ObjName, A.Name As FldName, B.Name As TypeName, A.Length
From SYSCOLUMNS A Inner Join SYSTYPES B On A.xtype = B.xtype
Inner Join SYSOBJECTS C ON A.ID = C.ID
Where C.xtype = 'U' And B.Name = @OldType
Declare @ObjName Varchar(128), @FldName Varchar(128), @TypeName Varchar(128), @Length Int Declare tItem cursor for
SELECT ObjName, FldName, TypeName, Length From @Tbl
Open tItem
FETCH NEXT FROM tItem into @ObjName, @FldName, @TypeName, @Length
WHILE @@FETCH_STATUS = 0
BEGIN
Exec ('Alter Table '+@ObjName+' Alter Column '+@FldName+' '+@NewType+'('+@Length+')')
FETCH NEXT FROM tItem into @ObjName, @FldName, @TypeName, @Length
END
CLOSE tItem
DEALLOCATE tItem
End
Go
写了个存储过程,这里没有考虑到当不带N的类型转带N类型的问题
比如: Varchar(5000) 转 NVarchar(5000) 这样的情况应该问出问题
给你个参考去修改吧
declare @num int,@tableName varchar(50),@columnName varchar(50),
@typeName varchar(50),@max_length int,@str varchar(500) = ''declare cur_table cursor for
with cte(num,tableName,columnName,typeName,max_length) as (
select row_number() over(order by ia.name asc) 'Num' ,
ia.name 'tableName',ib.name 'columnName',
ic.name 'typeName',ib.max_length
from test.sys.objects ia
inner join test.sys.columns ib
on ia.object_id = ib.object_id
inner join test.sys.types ic
on ib.user_type_id = ic.user_type_id
where ia.type ='U' and ia.name = 'abc'
) select num,tableName,columnName,typeName,max_length from cte;open cur_table
fetch next from cur_table into @num,@tableName,
@columnName,@typeName,@max_length
while(@@FETCH_STATUS = 0)
begin
if(@typeName = 'varchar')
begin
set @str = '
alter table '+@tableName+' alter column '+@columnName+' nvarchar('+str(@max_length)+')
'
exec(@str);
end
fetch next from cur_table into @num,@tableName,
@columnName,@typeName,@max_length
end
close cur_table;
deallocate cur_table;