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