declare @s varchar(8000),@tname varchar(100),@tid @intdeclare t_cursor cursor for select id,name from sysobjects where name like N'DAT%' and type = N'U'open t_cursorfetch next from t_cursor into @tid,@tnamewhile @@fetch_status = 0 begin set @s = 'select * from '+@tname+' where 1=1 '
select @s = @s + ' and '+name+'<0' from syscolumns where id = @tid and xtype in(48,52,56,59,60,62,106,108,127)
exec(@s)
fetch next from t_cursor into @tid,@tname endclose t_cursor deallocate t_cursor
上边一贴有误,修改如下: ---------------------------------------------------------------------- --定义局部变量 declare @s varchar(8000),@tname varchar(100),@tid @int--定义游标,用于取得表名为'DAT'开头的所有用户表的ID及名称 declare t_cursor cursor for select id,name from sysobjects where name like N'DAT%' and type = N'U'open t_cursorfetch next from t_cursor into @tid,@tnamewhile @@fetch_status = 0 begin --动态SQL语句拼装查询,查询where语句之后所跟字段应该为数字型 set @s = 'select * from '+@tname+' where 1=2 '
select @s = @s + ' or '+name+'<0' from syscolumns where id = @tid and xtype in(48,52,56,59,60,62,106,108,127)
exec(@s)
fetch next from t_cursor into @tid,@tname end--释放游标 close t_cursor deallocate t_cursor
select id,name from sysobjects where name like N'DAT%' and type = N'U'open t_cursorfetch next from t_cursor into @tid,@tnamewhile @@fetch_status = 0
begin
set @s = 'select * from '+@tname+' where 1=1 '
select
@s = @s + ' and '+name+'<0'
from
syscolumns
where
id = @tid
and
xtype in(48,52,56,59,60,62,106,108,127)
exec(@s)
fetch next from t_cursor into @tid,@tname
endclose t_cursor
deallocate t_cursor
----------------------------------------------------------------------
--定义局部变量
declare @s varchar(8000),@tname varchar(100),@tid @int--定义游标,用于取得表名为'DAT'开头的所有用户表的ID及名称
declare t_cursor cursor for
select id,name from sysobjects where name like N'DAT%' and type = N'U'open t_cursorfetch next from t_cursor into @tid,@tnamewhile @@fetch_status = 0
begin
--动态SQL语句拼装查询,查询where语句之后所跟字段应该为数字型
set @s = 'select * from '+@tname+' where 1=2 '
select
@s = @s + ' or '+name+'<0'
from
syscolumns
where
id = @tid
and
xtype in(48,52,56,59,60,62,106,108,127)
exec(@s)
fetch next from t_cursor into @tid,@tname
end--释放游标
close t_cursor
deallocate t_cursor