declare @rows int, @row int, @TabName varchar(128), @strSql varchar(512), @Column varchar(32), -- 要修改的列名 @DataType varchar(32) -- 要修改的类型 set @Column = 'name' set @DataType = 'varchar(128)' declare @t table (Row int identity(1,1),TableName varchar(128)) insert into @t select b.name from sys.columns a inner join sys.tables b on a.object_id = b.object_id where a.name = @Column set @rows = @@ROWCOUNT set @row = 1 while(@row <= @rows) begin select @TabName = TableName from @t where Row = @row set @strSql = ' alter table '+ @TabName +' alter column name ' + @DataType exec (@strSql) set @row = @row + 1 end
alter table 表 alter column 字段名 更改后的类型 --或者直接在企业管理器中修改
动态产生SQL语句,批量执行.select N'alter table ['+a.name+N' ] alter column ['+b.name+N'] varchar([长度]) ' from sys.tables a inner join sys.columns b on a.object_id=b.object_id where b.name='[字段名]'
alter column 字段名 更改后的类型
@row int,
@TabName varchar(128),
@strSql varchar(512),
@Column varchar(32), -- 要修改的列名
@DataType varchar(32) -- 要修改的类型
set @Column = 'name'
set @DataType = 'varchar(128)'
declare @t table (Row int identity(1,1),TableName varchar(128))
insert into @t
select b.name from sys.columns a inner join sys.tables b on a.object_id = b.object_id
where a.name = @Column
set @rows = @@ROWCOUNT
set @row = 1
while(@row <= @rows)
begin
select @TabName = TableName from @t where Row = @row set @strSql = ' alter table '+ @TabName
+' alter column name ' + @DataType
exec (@strSql)
set @row = @row + 1
end
alter table 表 alter column 字段名 更改后的类型
--或者直接在企业管理器中修改
from sys.tables a
inner join sys.columns b on a.object_id=b.object_id
where b.name='[字段名]'