22,209
社区成员
发帖
与我相关
我的任务
分享
USE test
GO
DECLARE @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) Null
Result :
命令已順利完成。
*/
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