declare @table nvarchar(50) declare cursor1 cursor for select name from sys.tables open cursor1 fetch next from cursor1 into @table while @@fetch_status=0 begin exec('alter table '+@table+' add newcol int') fetch next from cursor1 into @table,@col end close cursor1 deallocate cursor1
可以查询sys.tables批量生成语句然后修改。 select 'ALTER TABLE ' +NAME +' 更改COLUMN语句' from sys.tables
上面代码多了个@col变量,要去掉 如果你是2000的话,select name from sys.tables要换成 select name from sysobjects where xtype='U'
USE test GODECLARE @sql NVARCHAR(MAX)Select @sql=ISNULL(@sql+NCHAR(13)+NCHAR(10),'')+'Alter table '+QUOTENAME(name)+' Add test_col nvarchar(50) Null' from sys.sysobjects Where type='U'Exec (@sql) /* Print : Alter table PayList Add test_col nvarchar(50) Null Alter table test_tbA Add test_col nvarchar(50) NullResult : 命令已順利完成。 */ SELECT * FROM PayList /* ID test_col --- --------- 1 NULL 2 NULL */ SELECT * FROM test_tbA /* ID Val Val2 test_col --- ---- ------ -------- 1 a 7 NULL */
declare @table nvarchar(50)
declare cursor1 cursor for
select name from sys.tables
open cursor1
fetch next from cursor1 into @table
while @@fetch_status=0
begin
exec('alter table '+@table+' add newcol int')
fetch next from cursor1 into @table,@col
end
close cursor1
deallocate cursor1
select 'ALTER TABLE ' +NAME +' 更改COLUMN语句' from sys.tables
如果你是2000的话,select name from sys.tables要换成
select name from sysobjects where xtype='U'
GODECLARE @sql NVARCHAR(MAX)Select @sql=ISNULL(@sql+NCHAR(13)+NCHAR(10),'')+'Alter table '+QUOTENAME(name)+' Add test_col nvarchar(50) Null' from sys.sysobjects Where type='U'Exec (@sql)
/*
Print :
Alter table PayList Add test_col nvarchar(50) Null
Alter table test_tbA Add test_col nvarchar(50) NullResult :
命令已順利完成。
*/
SELECT * FROM PayList
/*
ID test_col
--- ---------
1 NULL
2 NULL
*/
SELECT * FROM test_tbA
/*
ID Val Val2 test_col
--- ---- ------ --------
1 a 7 NULL
*/