求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)
