牵着老婆满街逛

严以律己,宽以待人. 三思而后行.
GMail/GTalk: yanglinbo#google.com;
MSN/Email: tx7do#yahoo.com.cn;
QQ: 3 0 3 3 9 6 9 2 0 .

多分库多分表(结构相同)脚本创建联合视图

转载自: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]   
*/  

posted on 2017-02-16 11:10 杨粼波 阅读(888) 评论(0)  编辑 收藏 引用


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理