多分庫多分表（結構相同）指令碼建立聯合視圖

--今天有需要寫了一個--測試測下：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.table1select * from db1.dbo.table2select * from db1.dbo.table3select * from db2.dbo.table4select * from db2.dbo.table5select * from db2.dbo.table6select 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_tablecreate 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 forselect name from master.sys.databases where name like 'db[0-9]%' order by name --更改資料庫名open cur_dbfetch next from cur_db into @dbnamewhile @@FETCH_STATUS = 0beginset @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 @dbnameendclose cur_dbdeallocate cur_db--select * from #db_table--update #db_table set mk = 0--將各表建立合并視圖set nocount ondeclare @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)beginselect 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 outputset @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_nameendset @sql = left(@sql,LEN(@sql)-10)set nocount offprint(@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] */