if object_id('log20090927')is not null drop table log20090927 go create table log20090927( a int, b int,c int ) insert log20090927 select 1,3,4 union all select 2,2,2 if object_id('pro')is not null drop proc pro gocreate proc pro (@b int, @rq varchar(20)) as declare @s varchar(50) set @s='log'+@rq
set @s='select * from '+@s+' where b='+ltrim(@b) exec(@s) goexec pro 2,'20090927' ----也可以改成日期范围a b c ----------- ----------- ----------- 2 2 2(1 行受影响)
就是每周都生成一个类似于log20090927这样命名的表,有很多,大概几年了 表里有三个字段,id,username, logintime(简化) 然后想用存储来实现查询所有表,而不是某一张表 如select * from logXXXX where username=' 张三'; 因为表会增加,所以程序里没法写定表命 特请教
if object_id('log20090925')is not null drop table log20090925 go create table log20090925( a int, b int,c int ) insert log20090925 select 1,3,4 union all select 1,2,2if object_id('log20090927')is not null drop table log20090927 go create table log20090927( a int, b int,c int ) insert log20090927 select 1,3,4 union all select 2,2,2if object_id('tb')is not null drop table tb ----存放结果 go create table tb( a int, b int,c int ) ------------------------------------------------- if object_id('pro')is not null drop proc pro gocreate proc pro (@b int ) as declare @s varchar(50),@tb_name varchar(15)
declare cur cursor for select name from sysobjects where name like 'log%' and type='u' --- 类似log 开头的表名 open cur fetch next from cur into @tb_name while @@fetch_status=0 begin set @s='select * from '+@tb_name+' where b='+ltrim(@b) insert tb exec(@s)
fetch next from cur into @tb_name end close cur deallocate cur goexec pro 2 ----也可以改成日期范围select * from tba b c ----------- ----------- ----------- 1 2 2 2 2 2(2 行受影响)
谢谢js_szy 大概就是这个意思了 我下午再实地测试下 感谢
alter procedure pro_unionalltb /* EXEC pro_unionalltb */ as declare @tbname VARCHAR(40) declare @tbnames VARCHAR(4000)begin set @tbnames='' DECLARE @MyData CURSOR SET @MyData = CURSOR FOR select b.[name] from sys.objects b where b.type='U' AND b.[name] LIKE 'LOG%' OPEN @MyData FETCH NEXT FROM @MyData INTO @tbname WHILE @@FETCH_STATUS = 0 BEGIN set @tbnames=@tbnames+' UNION ALL SELECT * from ['+@tbname+']' FETCH NEXT FROM @MyData INTO @tbname END CLOSE @MyData DEALLOCATE @MyData SET @tbnames=SUBSTRING(@tbnames,11,len(@tbnames)-10) PRINT @tbnames EXEC(@tbnames)END
drop table log20090927 drop table log20090928 drop table log20090929create table log20090927(Id int identity(1,2), name varchar(10)); create table log20090928(Id int identity(1,2), name varchar(10)); create table log20090929(Id int identity(1,2), name varchar(10));insert into log20090927 values('luoyoumou1'); insert into log20090928 values('luoyoumou2'); insert into log20090929 values('luoyoumou3');alter procedure pro_unionalltb /* EXEC pro_unionalltb */ as declare @tbname VARCHAR(40) declare @tbnames VARCHAR(4000)begin set @tbnames='' DECLARE @MyData CURSOR SET @MyData = CURSOR FOR select b.[name] from sys.objects b where b.type='U' AND b.[name] LIKE 'LOG%' OPEN @MyData FETCH NEXT FROM @MyData INTO @tbname WHILE @@FETCH_STATUS = 0 BEGIN set @tbnames=@tbnames+' UNION ALL SELECT * from ['+@tbname+']' FETCH NEXT FROM @MyData INTO @tbname END CLOSE @MyData DEALLOCATE @MyData SET @tbnames=SUBSTRING(@tbnames,11,len(@tbnames)-10) PRINT @tbnames EXEC(@tbnames)END
if object_id('log20090927')is not null drop table log20090927
go
create table log20090927( a int, b int,c int )
insert log20090927 select
1,3,4 union all select
2,2,2
if object_id('pro')is not null drop proc pro
gocreate proc pro
(@b int, @rq varchar(20))
as
declare @s varchar(50)
set @s='log'+@rq
set @s='select * from '+@s+' where b='+ltrim(@b)
exec(@s) goexec pro 2,'20090927' ----也可以改成日期范围a b c
----------- ----------- -----------
2 2 2(1 行受影响)
但是你这个是传递参数查某一周的表
我是想联合起来
把所有这样按日期命名的表都查询了
表里有三个字段,id,username, logintime(简化)
然后想用存储来实现查询所有表,而不是某一张表
如select * from logXXXX where username=' 张三';
因为表会增加,所以程序里没法写定表命
特请教
if object_id('log20090925')is not null drop table log20090925
go
create table log20090925( a int, b int,c int )
insert log20090925 select
1,3,4 union all select
1,2,2if object_id('log20090927')is not null drop table log20090927
go
create table log20090927( a int, b int,c int )
insert log20090927 select
1,3,4 union all select
2,2,2if object_id('tb')is not null drop table tb ----存放结果
go
create table tb( a int, b int,c int )
-------------------------------------------------
if object_id('pro')is not null drop proc pro
gocreate proc pro
(@b int )
as
declare @s varchar(50),@tb_name varchar(15)
declare cur cursor for select name from sysobjects where name like 'log%' and type='u' --- 类似log 开头的表名
open cur
fetch next from cur into @tb_name
while @@fetch_status=0
begin
set @s='select * from '+@tb_name+' where b='+ltrim(@b)
insert tb exec(@s)
fetch next from cur into @tb_name
end
close cur
deallocate cur
goexec pro 2 ----也可以改成日期范围select * from tba b c
----------- ----------- -----------
1 2 2
2 2 2(2 行受影响)
大概就是这个意思了
我下午再实地测试下
感谢
/*
EXEC pro_unionalltb
*/
as
declare @tbname VARCHAR(40)
declare @tbnames VARCHAR(4000)begin
set @tbnames='' DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
select b.[name] from sys.objects b where b.type='U' AND b.[name] LIKE 'LOG%'
OPEN @MyData
FETCH NEXT FROM @MyData INTO @tbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @tbnames=@tbnames+' UNION ALL SELECT * from ['+@tbname+']'
FETCH NEXT FROM @MyData INTO @tbname
END
CLOSE @MyData
DEALLOCATE @MyData SET @tbnames=SUBSTRING(@tbnames,11,len(@tbnames)-10)
PRINT @tbnames
EXEC(@tbnames)END
drop table log20090928
drop table log20090929create table log20090927(Id int identity(1,2), name varchar(10));
create table log20090928(Id int identity(1,2), name varchar(10));
create table log20090929(Id int identity(1,2), name varchar(10));insert into log20090927 values('luoyoumou1');
insert into log20090928 values('luoyoumou2');
insert into log20090929 values('luoyoumou3');alter procedure pro_unionalltb
/*
EXEC pro_unionalltb
*/
as
declare @tbname VARCHAR(40)
declare @tbnames VARCHAR(4000)begin
set @tbnames='' DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
select b.[name] from sys.objects b where b.type='U' AND b.[name] LIKE 'LOG%'
OPEN @MyData
FETCH NEXT FROM @MyData INTO @tbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @tbnames=@tbnames+' UNION ALL SELECT * from ['+@tbname+']'
FETCH NEXT FROM @MyData INTO @tbname
END
CLOSE @MyData
DEALLOCATE @MyData SET @tbnames=SUBSTRING(@tbnames,11,len(@tbnames)-10)
PRINT @tbnames
EXEC(@tbnames)END
同时也谢谢各位的支持
谢谢