日期:2014-05-18  浏览次数:20757 次

求SQL语句(关于借助系统表的问题)
我想把一数据库中所有用户表中如果有ProID的字段,   那么删除所有ProID= 'P001 '   的数据。
请指点。

------解决方案--------------------
---先得到符合条件的表

select o.name from sysobjects o
inner join syscolumns c on c.name= 'ProID ' and o.id=c.id
------解决方案--------------------
declare @s varchar(8000)
set @s= ' '
select @s=@s+ 'Delete from '+o.name + ' where ProID= ' 'P001 ' ' '+char(10) from sysobjects o
inner join syscolumns c on c.name= 'ProID ' and o.id=c.id
where o.xtype= 'u '
--print @s
exec(@s)
------解决方案--------------------
没有了,只能用游标。
------解决方案--------------------
rockyljt(江濤) ( ) 信誉:100 Blog 加为好友 2007-05-28 15:38:59 得分: 0


declare @s varchar(8000)
set @s= ' '
select @s=@s+ 'Delete from '+o.name + ' where ProID= ' 'P001 ' ' '+char(10) from sysobjects o
inner join syscolumns c on c.name= 'ProID ' and o.id=c.id
where o.xtype= 'u '
--print @s
exec(@s)


--------------------------------
这大哥以说了~
------解决方案--------------------
declare @table_name varchar(100)
declare @sql varchar(8000)
declare cur_gettablename cursor for
select name from sysobjects where type= 'u ' and id exists(select id from syscolumns where name= 'proid ')
open cur_gettablename
fetch cur_gettablename into @table_name
while @@fetch_status=0
begin
set @sql=isnull(@sql, ' ')+ 'delete from '+@table_name+ 'where proid= ' 'p001 ' ' '
exec(@sql)
fetch cur_gettablename into @table_name
end
close cur_gettablename
deallocate cur_gettablename
------解决方案--------------------
declare @s varchar(8000)
set @s= ' '
select @s=@s+ 'Delete from '+o.name + ' where ProID= ' 'P001 ' ' '+char(10) from sysobjects o
inner join syscolumns c on c.name= 'ProID ' and o.id=c.id
where o.xtype= 'u '
--print @s
exec(@s)