有多个数据库名称为ABC201101,ABC201102,ABC201103……,他们中表的结构相同,每个数据库中都有一张表TTT20110101,TTT20110102由于数据库比较多,我想简单写段查询语句来实现对每个数据库中的表TTT20110101与TTT20110102中的数据进行查找(列举所有字段),TTT20110101与TTT20110102表结构一样,请高手指教,用函数可以实现吗?谢谢!
调试欢乐多
union all
select * from [B库]..tb
create procedure proc_test
AS
BEGIN
set nocount on
declare @sql varchar(8000);
declare @id int;
declare @dbname varchar(30);
declare @tb table(id identity(1,1) int,dbname varchar(100))
insert into @tb(dbname)
select 'db1'
union all
select 'db2'
union all
select 'db3'
union all
select 'db4'
--... 连你的各种数据库
set @sql=""
select top 1 @id,=id,@dbname=dbname from @tb order by id desc
while isnull(@id,0)<>0
begin
set @sql=@sql+'select * from '+@tb+'..TTT20110101 union all select * from '+@tb+'..TTT20110102 '
if @id<>1
set @sql=@sql+' union all '
delete from @tb where id=@id
select top 1 @id,=id,@dbname=dbname from @tb order by id desc
end
exec (@sql)
END--调用方式: exec proc_test
--试下,我没测- -
如果数据库非常多的话 可以用动态语句
Select * From 机器名.ABC201101.dbo.TTT20110101
Union
Select * From 机器名.ABC201102.dbo.TTT20110101
……