Multi-Library multi-table (structure identical) script Create federated View

--Today there is a need to write a-test: Create DATABASE [DB1] CREATE DATABASE [DB2] use [DB1] Create TABLE [dbo]. [Table1] ([id] [int],[name] [varchar]) CREATE TABLE [dbo]. [Table2] ([id] [int],[name] [varchar]) CREATE TABLE [dbo]. [Table3] ([id] [int],[name] [varchar]) use [DB2] CREATE TABLE [dbo]. [Table4] ([id] [int],[name] [varchar]) CREATE TABLE [dbo]. [Table5] ([id] [int],[name] [varchar]) CREATE TABLE [dbo]. [Table6] ([id] [int],[name] [varchar] (20))--the format is as follows (comparison rule.) ): SELECT * FROM db1.dbo.table1 SELECT * Db1.dbo.table2 SELECT * to Db1.dbo.table3 select * from Db2.dbo.table4 SE  Lect * FROM Db2.dbo.table5 SELECT * from Db2.dbo.table6 select name to master.sys.databases where name like ' db[0-9]% ' --Database name format select name from sys.tables where name like ' table[0-9]% '-table name format-is not created dynamically, it needs to be manually specified: database format name. 

Table-style name, view name--Associates database name with table name--drop table #db_table CREATE TABLE #db_table (dbname varchar (), tabname varchar, MK bit)
DECLARE @dbname varchar DECLARE @exec varchar (max)Set @exec = ' Declare cur_db cursor for select name to master.sys.databases where name like ' db[0-9]% ' ORDER by name-- Change database name Open cur_db fetch NEXT from cur_db to @dbname while @ @FETCH_STATUS = 0 Begin Set @exec = ' SELECT ' + @dbname + ' ' , name,0 from [' + @dbname + '].sys.tables where name is ' table[0-9]% ' ' ORDER by name '--Change table name insert INTO #db_table exec (@e XEC) fetch NEXT from cur_db to @dbname end close cur_db deallocate cur_db--The SELECT * FROM #db_table--Update #db_tab  Le set mk = 0-Create merged views of each table SET NOCOUNT on declare @db_name Nvarchar (a) Declare @tab_name Nvarchar (a) Declare @col_name Nvarchar (4000) declare @sql NVARCHAR (max) declare @sql1 Nvarchar (max) Set @sql1 = N ' Set @col_name = N ' Set @sql = N ' C  reate view V_table_all ' +char + ' as ' +char (10)--Change the view name while exists (SELECT * from #db_table where mk=0) begin Select Top 1 @db_name =dbname, @tab_name the =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 (a) update #db_table set mk = 1 where dbname= @db_name and tabname= @tab_name End set @sq L = Left (@sql, LEN (@sql) -10) set nocount out print (@sql)--exec (@sql)/*--output: 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] * *