轉載自：http://blog.csdn.net/kk185800961/article/details/41677109--今天有需要寫了一個
--測試測下：
CREATE DATABASE [db1]
CREATE DATABASE [db2]
USE [db1]
CREATE TABLE [dbo].[table1]([id] [int],[name] [varchar](20))
CREATE TABLE [dbo].[table2]([id] [int],[name] [varchar](20))
CREATE TABLE [dbo].[table3]([id] [int],[name] [varchar](20))
USE [db2]
CREATE TABLE [dbo].[table4]([id] [int],[name] [varchar](20))
CREATE TABLE [dbo].[table5]([id] [int],[name] [varchar](20))
CREATE TABLE [dbo].[table6]([id] [int],[name] [varchar](20))
--格式如下（比較規律！）：
select * from db1.dbo.table1
select * from db1.dbo.table2
select * from db1.dbo.table3
select * from db2.dbo.table4
select * from db2.dbo.table5
select * from db2.dbo.table6
select name from master.sys.databases where name like 'db[0-9]%' --數據庫名稱格式
select name from sys.tables where name like 'table[0-9]%' --表名稱格式
--不是動態創建，需手動指定：數據庫格式名。表格式名，視圖名稱
--將數據庫名和表名關聯
-- drop table #db_table
create table #db_table(dbname varchar(50),tabname varchar(50),mk bit)
declare @dbname varchar(50)
declare @exec varchar(max)
set @exec = ''
declare cur_db cursor for
select name from master.sys.databases where name like 'db[0-9]%' order by name --更改數據庫名
open cur_db
fetch next from cur_db into @dbname
while @@FETCH_STATUS = 0
begin
set @exec = 'select '''+@dbname+''',name,0 from ['+@dbname+'].sys.tables where name like ''table[0-9]%'' order by name '--更改表名
insert into #db_table exec(@exec)
fetch next from cur_db into @dbname
end
close cur_db
deallocate cur_db
-- select * from #db_table
-- update #db_table set mk = 0
--將各表創建合并視圖
set nocount on
declare @db_name Nvarchar(50)
declare @tab_name Nvarchar(50)
declare @col_name Nvarchar(4000)
declare @sql Nvarchar(max)
declare @sql1 Nvarchar(max)
set @sql1 = N''
set @col_name = N''
set @sql = N' create view v_table_all '+CHAR(10)+' as '+CHAR(10) --更改視圖名稱
while exists(select * from #db_table where mk=0)
begin
select top 1 @db_name=dbname,@tab_name=tabname from #db_table where mk=0
set @col_name = ''
set @sql1 = N'select @col_name = @col_name + name+'','' from ['+@db_name+'].sys.columns where object_id=object_id(''['+@db_name+'].dbo.['+@tab_name+']'')'
exec sp_executesql @sql1,N'@col_name varchar(4000) output',@col_name =@col_name output
set @col_name = left(@col_name,LEN(@col_name)-1)
set @sql = @sql + ' select '+@col_name+' from ['+@db_name+'].dbo.['+@tab_name+'] union all'+CHAR(10)
update #db_table set mk = 1 where dbname=@db_name and tabname=@tab_name
end
set @sql = left(@sql,LEN(@sql)-10)
set nocount off
print(@sql)
--exec(@sql)
/*--輸出結果：
create view v_table_all
as
select id,name from [db1].dbo.[table1] union all
select id,name from [db1].dbo.[table2] union all
select id,name from [db1].dbo.[table3] union all
select id,name from [db2].dbo.[table4] union all
select id,name from [db2].dbo.[table5] union all
select id,name from [db2].dbo.[table6]
*/