SQL中怎样更改数据库中所有表中所有相同字段的长度或数据类型
比如将数据库的所有表中包括名字为EmpNo的字段的长度由原来的8位变为10，
将另一个字段EID的数据类型由char变为varchar，且长度由15变为18


Tks!



------解决方案--------------------
declare @sql nvarchar(4000)
set @sql=N 'if exists(select 1 from syscolumns where id=object_id(?) and name= 'declare @sql varchar(4000)
declare @c varchar(100)
set @c= 'EmpNo '


set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id( ' '? ' ') and name= ' ' '+@c+ ' ' ')
alter table ? alter column '+ @c + ' varchar(10) not null '

exec sp_MSforeachtable @sql, '? '

字段EID的数据类型由char变为varchar，且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
------解决方案--------------------
declare @sqlstr varchar(4000),
@FieldName sysname

select @Sqlstr = ' ', @FieldName = 'f1 '

SELECT @SQLStr = @Sqlstr + ' alter table ' + b.name + ' alter column '+@Fieldname + ' varchar(20) '
from syscolumns a
left join sysobjects b on a.id = b.id
where a.name = @FieldName

print(@Sqlstr)
exec(@Sqlstr)

------解决方案--------------------
declare @name varchar(50)
declare @sql varchar(100)
declare y CURSOR
for select name from sysobjects where xtype= 'u ' and name <> 'dtproperties '
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name= 'EmpNo ')
Begin
set @sql= 'alter table '+ @name+ ' alter column EmpNo char(10) '

exec(@sql)
End

if exists(select * from syscolumns where id=object_id(@name) and name= 'EID ')
Begin
set @sql= 'alter table '+ @name+ ' alter EID column varchar(15) '

exec(@sql)
End

FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y