请求一个存储过程

[dba_sunny]

数据库里有post_200605,post_200606,.......post_200***(以当前月份结束),post一系列表.表结构一致.怎么做一个循环于存储过程中,将所有的表格union all起来呢?

[dba_sunny]

能解释一下吗?

[dba_sunny]

看不太懂~~

[dba_sunny]

感觉好像不对的

[gahade]

create proc usp_demo
as
begin
declare @i int
declare @olddbname varchar(50)
declare @dbname varchar(50)
declare @tablename varchar(50)
declare @sql varchar(8000)
select @sql = ''
select @i = 1
--得到数据库名
create table #t_database(dbname varchar(50),dbsize int,remarks varchar(255))
insert into #t_database(dbname,dbsize,remarks)
exec sp_databases
--取表名(因数据库结构是一样,所以随便取一个数据库中的表)
select name into #t_table from post_200605..sysobjects where type = 'U'
--取数据库名
declare dbname cursor local for
select dbname from #t_database where left(dbname,4) = 'post'
open dbname
fetch next from dbname into @dbname
while @@fetch_status = 0
begin
--第一次执行时不加union all
if @i = 1
begin
update #t_table
set name = 'select * from '+@dbname+'..'+name
end
else --以后就加union all
begin
update #t_table
set name = name + ' union all ' + replace(names,@olddbname,@dbname)+' union all '
end
select @olddbname = @dbname
select @i = @i + 1
fetch next from dbname into @dbname
end
close dbname
DEALLOCATE dbname
--将所有的语句组合在一起
select @sql = ''
select @sql = @sql + name from #t_table
--去掉最后的union all
if len(@sql) > 0
begin
select @sql = left(@sql,len(@sql) - len(' union all '))
end
--执行
exec(@sql)
drop table #t_database
drop table #t_table
end


--注:未经过测试
exec usp_demo

[gahade]

晕,看错了.以为post_200605是数据库呢,结果做的是所有数据库中所有表联合了!
更正下

create proc usp_demo
as
begin
declare @tablename varchar(50)
declare @sql varchar(8000)
select @sql = ''
--取表名
declare tablename cursor local for
select name from sysobjects where left(name,4) = 'post' and type = 'U'
open tablename
fetch next from tablename into @tablename
while @@fetch_status = 0
begin
select @sql = @sql + 'select * from '+@tablename + ' union all '
fetch next from tablename into @tablename
end
close tablename
DEALLOCATE tablename
--去掉最后的union all
if len(@sql) > 0
begin
select @sql = left(@sql,len(@sql) - len(' union all '))
end
--执行
exec(@sql)
end

[dba_sunny]

好像用游标不好的!~~数据库太大了,不利于优化!~~

[gahade]

游标只是组合SQL语句.

速度快慢取决于你数据库的大小

建索引

[liangpei2008]

dba_sunny
感觉好像不对的
--------------
哪不对，说来听听！

[dba_sunny]

post表也要union all的.例如:这个月的某一天执行的时候,执行的结果就是:
select * from post_200605
union all
select * from post_200606
union all
select * from post_200607
union all
select * from post_200608
union all
select * from post
下个月的某一天执行的时候,执行的结果就是:
select * from post_200605
union all
select * from post_200606
union all
select * from post_200607
union all
select * from post_200608
union all
select * from post_200609
union all
select * from post
上个月的某一天执行的时候,执行的结果就是:
select * from post_200605
union all
select * from post_200606
union all
select * from post_200607
union all
select * from post

[gahade]

上面的哪天是天啊?不都是月吗?

[gahade]

上面的那个存储过程不是可以实现吗?已经包含了post表了啊!
如果你要限制表范围,给存储过程加参数吧

[dba_sunny]

急
!!

[liangpei2008]

CREATE PROC PROC1
AS
DECLARE @T VARCHAR(8000)
SET @T=''
SELECT @T=@T+'SELECT * FROM '+'['+NAME+']'+' UNION ALL '
FROM SYSOBJECTS WHERE XTYPE='U' AND LEFT(NAME,4)='POST' AND RIGHT(NAME,6)=CONVERT(VARCHAR(6),GETDATE(),112)
SELECT @T=STUFF(@T ,LEN(@T)-9,10,'')
EXEC (@T)