如果你不想用日期作为表名,要用如a1,a2,a3这样的格式,那么在保证数字前面的字符在数据表中的唯一性的前提下,可以用如下方法: 前提:表前面的字符aabbccdd1,aabbccdd2,aabbccdd3declare @tn varchar(100),@sql varchar(1000) select top 1 @tn=name from sysobjects where xtype='u' and left(name,8)=aabbccdd order by name desc set @sql='select * from '+@tn exec(@sql) 反正不可能一句SQL就能解决的了
--如果每天生成一个表 declare @tablename varchar(100) select @tablename=[name] from sysobjects where xtype='U' and convert(varchar(10),crdate,120)=convert(varchar(10),getdate(),120) exec('select * from '+@tablename)
刚少了两个单引号,重新补充上:declare @tn varchar(100),@sql varchar(1000) select top 1 @tn=name from sysobjects where xtype='u' and left(name,8)='aabbccdd' order by name desc set @sql='select * from '+@tn exec(@sql)
declare @name varchar(100) declare cur_1 cursor for select [name] from sysobjects where xtype='U' open cur_1 fetch next from cur_1 into @name while @@FETCH_STATUS begin exec('select * from '+@name) fetch next from cur_1 into @name endclose cur_1 deallocate cur_1
前提:表前面的字符aabbccdd1,aabbccdd2,aabbccdd3declare @tn varchar(100),@sql varchar(1000)
select top 1 @tn=name from sysobjects where xtype='u' and left(name,8)=aabbccdd order by name desc
set @sql='select * from '+@tn
exec(@sql)
反正不可能一句SQL就能解决的了
declare @tablename varchar(100)
select @tablename=[name] from sysobjects where xtype='U' and convert(varchar(10),crdate,120)=convert(varchar(10),getdate(),120)
exec('select * from '+@tablename)
select top 1 @tn=name from sysobjects where xtype='u' and left(name,8)='aabbccdd' order by name desc
set @sql='select * from '+@tn
exec(@sql)
---------
用游标或while都可以,我想不明白你要查所有表的目的是什么?
declare cur_1 cursor for
select [name] from sysobjects where xtype='U'
open cur_1
fetch next from cur_1 into @name
while @@FETCH_STATUS
begin
exec('select * from '+@name)
fetch next from cur_1 into @name
endclose cur_1
deallocate cur_1