数据库中删除列

[redstorm11]

我的数据库db中每表都有一个rowguid字段，我想删除这列，请问sql怎么写！

[Softlee81307]

alter table 表 drop column rowguid

[redstorm11]

我是说删除所有表中的那个字段，
alter table 表 drop column rowguid 不是每个表都要写一次了，
我想能不能有一条语句就把那个字段全部删除。

[LBYYBL]

declare @id int
declare @nob int
declare @tname nvarchar(100)
select id=identity(int,1,1),name into #t from dbname..sysobjects where xtype='U'
set @id=1
set @nob=(select max(id) from #t)
set @tname=(select name from #t where id=@id)
while @id<=@nob
begin
exec('alter table '+@tname+' drop column rowguid')
set @id=@id+1
set @tname=(select name from #t where id=@id)
end

[flybox728]

用循环吧

select a.name,b.name from sysobjects a ,syscolumns b where a.id=b.id and a.xtype='u' and b.name='rowguid'

这样可以找出全部有　rowguid　字段的表
然后再在循环里
alter table 表 drop column rowguid

[Softlee81307]

declare @name varchar(20),@s varchar(1000)
declare kk cursor for
select bb=object_name(id) from syscolumns where objectproperty(id,'isUserTable')=1
and object_name(id)<>'dtproperties' and name='rowguid'
open kk
fetch next from kk into @name
while @@fetch_status=0
begin
set @s='alter table '+@name+' drop column rowguid '
exec(@s)
fetch next from kk into @name
end
close kk
deallocate kk

[wzjcntlqs]

SELECT top 100 PERCENT
TABLE_CODE=d.name,
COLUMNS_CODE=a.name
FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where a.name='字段名'
order by a.id,a.colorder
得到所有的表名，再执行删除字段

[rfq]

ALTER procedure dropCol
@strcol varchar(100)
as
declare cur_opentab cursor
for
select a.name from sysobjects a,syscolumns b where a.id=b.id and OBJECTPROPERTY(a.id,'IsUserTable')=1 and b.name=@strcol
declare @strtab varchar(100),@str varchar(100)
set @str=''
set @strtab=''
open cur_opentab
fetch next from cur_opentab into @strtab
while @@fetch_status=0
begin
set @str='alter table '+@strtab+' drop column '+@strcol
print @str
exec(@str)
set @str=''
fetch next from cur_opentab into @strtab
end
close cur_opentab
deallocate cur_opentab




GO

[lengxiaowei]

exec sp_msforeachtable @command1=N'
declare @s nvarchar(4000),@tbname sysname
select @s=N'''',@tbname=N''?''
if exists(select 1 from syscolumns where id=object_id(@tbname) and name=''ddd'')
if exists(select 1 from (select count(*) as tcount from syscolumns
where id=object_id(@tbname)) a where a.tcount>1)
exec(N''alter table ''+@tbname+'' drop column ddd '')
else
exec(N''drop table ''+@tbname)'

[LoveLwn]

declare @id int
declare @nob int
declare @tname nvarchar(100)
select id=identity(int,1,1),name into #t from dbname..sysobjects where xtype='U'
set @id=1
set @nob=(select max(id) from #t)
set @tname=(select name from #t where id=@id)
while @id<=@nob
begin
exec('alter table '+@tname+' drop column rowguid')
set @id=@id+1
set @tname=(select name from #t where id=@id)
end