用sql语句添加新列的问题
我有一些表，表名有规律：商业用户_用户A、商业用户_用户B、商业用户_用户C……等，我想在这些表的ID列后共同添加一个名为NAME的列，varchar(8)，列的内容就是这些用户的名字，如用户A、用户B等。怎样实现？

------解决方案--------------------
declare @tabname varchar(50)
declare @str varchar(8000)
declare cur_tab cursor for
select name from sysobjects where name like '%商业用户_用户% '
open cur_tab
fetch next from cur_tab into @tabname
while @@fetch_status=0
begin
set @str= 'alter table '+@tabname+ ' add [name] varchar(8) default( '+char(39)+substring(@tabname,charindex( '_ ',@tabname)+1,1000)+char(39)+ ') '
set @str=@str+ 'update '+@tabname+ ' set [name]= '+char(39)+substring(@tabname,charindex( '_ ',@tabname)+1,1000)+char(39)
exec(@str)
end
close cur_tab
deallocate cur_tab
------解决方案--------------------

--可重复执行的SQL代码

declare @sql varchar(8000)

declare cur cursor read_only
for
select name from sysobjects where name like '商业用户_用户% '

declare @name varchar(40)
open cur

fetch next from cur into @name
while (@@fetch_status = 0)
begin

set @sql= ' if not exists(select a.name,b.name from dbo.sysobjects a inner join syscolumns b on a.id=b.id where a.name= ' ' ' + @name + ' ' ' and b.name= ' 'name ' ') alter table [ ' + @name + '] add [name] varchar(8) '
exec(@sql)

set @sql= 'update [ ' + @name + '] set [name]= ' ' ' + replace(@name, '商业用户_ ', ' ') + ' ' ' '
exec(@sql)

fetch next from cur into @name
end

close cur
deallocate cur
go


------解决方案--------------------
-- 加列
EXEC sp_msforeachtable
@command1 = N '
DECLARE
ALTER TABLE ? ADD
NAME varchar(8)
',
@whereand = N '
AND O.name LIKE N ' '商业用户[_]用户% ' '
'

-- 设置数据
EXEC sp_msforeachtable
@command1 = N '
UPDATE ? SET
NAME = RIGHT(N ' '? ' ', 3)
ALTER TABLE ? ADD
NAME varchar(8)
',
@whereand = N '
AND O.name LIKE N ' '商业用户[_]用户% ' '
'
------解决方案--------------------
declare hcforeach cursor global
for
select 'alter table '+quotename(name, ' ') + ' add [name] varchar(8) null '

from sysobjects
where objectproperty(id, 'isusertable ')=1
and name like '商业用户[_]用户% '
exec sp_msforeach_worker '? '