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)
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,res varchar(255)) insert into #t_database(dbname,dbsize,res) 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
晕,看错了.以为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
游标只是组合SQL语句.速度快慢取决于你数据库的大小建索引
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
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)
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,res varchar(255))
insert into #t_database(dbname,dbsize,res)
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
更正下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
感觉好像不对的
--------------
哪不对,说来听听!
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
如果你要限制表范围,给存储过程加参数吧