用循环搞定:
declare @tb varchar(100),@colname varchar(100)
while exists(SELECT d.name,a.name,b.name
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
where b.name='money')
begin
SELECT @tb=d.name,@colname=a.name
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
where b.name='money'
exec('alter table '+@tb+' alter column '+@colname+' numeric(10,2)')
end
declare tb cursor for
SELECT sql='alter table ['+d.name
+'] alter column ['+a.name+'] numeric(10,2))'
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where
b.name='money'
and
not exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) --主键不能修改
order by d.name,a.name
declare @sql varchar(1000)
open tb
fetch next from tb into @sql
while @@fetch_status = 0
begin
exec(@sql)
fetch next from tb into @sql
end
close tb
deallocate tb
例:表table2中的列id为int型,现改为char型
alter table table2 alter column id char
3.增加列
A.例:在表table2中增加name列为char型
alter table table2 add name char(8)
B.例:在表table2中增加nob列为int型且设自增量为1,且不空
alter table table2 add nob int identity(1,1) not null