USE [TestDataBase]
GO
/****** Object: StoredProcedure [dbo].[PT_CreateMineDatabaseBak] Script Date: 2018/7/27 16:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 2017-4-24
-- Edit time：2017-5-16
-- Edit time：2017-11-2 新增字段默认值、字段顺序复制
-- Description: 实现集成平台数据库复制存储功能
-- 使用方法：在导入目标服务器任意数据库中执行
-- =============================================
ALTER PROCEDURE [dbo].[PT_CreateMineDatabaseBak]
@mineName NVARCHAR(50)='煤矿',--源矿井名称
@meg NVARCHAR(500) = '' OUTPUT
AS
DECLARE
@mineDesc VARCHAR(50),--矿井描述
@sql NVARCHAR(max),--sql语句
@tbName varchar(50),--正在生成表的表名
@allCount int,--所有（用户表/视图）个数
@realNum int,--正在生成第*个（表/视图）
@failNum int,--失败*个（表/视图）
@keyName varchar(50),--正在生成表主键名称
@blCon int = 1, --1远程连接服务器成功，2失败
@path VARCHAR(8000), --得到当前数据库的数据文件路径
@bsl varchar(100),--标识列
@bszz varchar(50),--标识种子
@bsdz varchar(50),--标识递增量
@tbCol varchar(5000)
BEGIN
set @realNum = 1
set @failNum = 0
set @keyName = ''
--得到当前数据库的数据文件路径
SELECT @path = RTRIM(REVERSE(FILENAME)) FROM sysfiles
SELECT @path = REVERSE(SUBSTRING(@path, CHARINDEX('\', @path), 8000))
if (@mineName is null or @mineName = '')
begin
set @meg = '数据库名称不能为空'
print (@meg)
return 0
end
else
begin
if Exists(select 1 From master.dbo.sysdatabases where name='TestDataBase')--存在数据库'TestDataBase'
begin
if Exists(select 1 From [TestDataBase].dbo.sysobjects where name='Sys_Mine')--存在表'Sys_Mine'
begin
if Exists(select 1 from [TestDataBase].dbo.Sys_Mine where MineName = @mineName)--源数据库中是否存在该矿井
begin
update [TestDataBase].dbo.Sys_Mine set CurrentMine = 1 where MineName = @mineName--设置矿井为当前矿井
--2018年6月23日14:25:43 Doyle 注释掉，采用统一数据库名称
--select @mineDesc = MineDesc from [TestDataBase].dbo.Sys_Mine where MineName = @mineName--获取矿井描述
set @mineDesc='M';
if exists(select 1 from master.dbo.sysdatabases where name='Platform_'+@mineDesc+'_v3.0')--目标数据库已存在
begin
set @sql = 'drop database [Platform_'+@mineDesc+'_v3.0]'--删除目标数据库(先清空正在使用,再删除)
BEGIN TRY
DECLARE @ks NVARCHAR(1000)
DECLARE tb CURSOR LOCAL
FOR
SELECT ks = 'kill ' + CAST(spid AS VARCHAR)
FROM MASTER..sysprocesses
WHERE dbid = DB_ID('Platform_'+@mineDesc+'_v3.0')
OPEN tb
FETCH NEXT FROM tb INTO @ks
WHILE @@fetch_status = 0
BEGIN
EXEC (@ks)
FETCH NEXT FROM tb INTO @ks
END
CLOSE tb
DEALLOCATE tb
exec (@sql)
end try
begin catch
set @meg = '删除目标数据库失败:'+ERROR_MESSAGE()
print (@meg)
return 0
end catch
end
set @sql = 'CREATE DATABASE [Platform_'+@mineDesc+'_v3.0] ON PRIMARY
(NAME = ''Platform_'+@mineDesc+'_v3.0'', FILENAME = '''+@path+'Platform_'+ @mineDesc+'_v3.0.mdf'' , SIZE = 5000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = ''Platform_'+@mineDesc+'_v3.0_Log'', FILENAME = '''+@path+'Platform_'+ @mineDesc+'_v3.0_log.LDF'' , SIZE = 1000KB , MAXSIZE = 1000000KB , FILEGROWTH = 5%)'
exec (@sql)--新建目标数据库
print ('创建数据库成功：'+ @path+'Platform_'+ @mineDesc+'_v3.0.mdf')
select @allCount = COUNT(1) from [TestDataBase].dbo.sysobjects where xtype='U'
DECLARE cursor_fkeyCols CURSOR FOR select name from [TestDataBase].dbo.sysobjects where xtype='U'
OPEN cursor_fkeyCols
FETCH NEXT FROM cursor_fkeyCols INTO @tbName
WHILE @@FETCH_STATUS = 0
BEGIN
print( '复制进度:总数/当前:'+convert(varchar,@allCount)+','+convert(varchar,@realNum)) + ' 表名称：' + @tbName--复制进度
set @sql = 'select * into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' from [TestDataBase].dbo.'+@tbName + ' where 1<>1'
BEGIN TRY
exec (@sql)--复制表结构
select @keyName = stuff((SELECT ','+a.name
FROM [TestDataBase].dbo.syscolumns a
inner join [TestDataBase].dbo.sysobjects d on a.id=d.id
where d.name=@tbName and exists(SELECT 1 FROM [TestDataBase].dbo.sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM [TestDataBase].dbo.sysindexes WHERE indid in(
SELECT indid FROM [TestDataBase].dbo.sysindexkeys WHERE id = a.id AND colid=a.colid
))) FOR XML PATH('')),1,1,'')
begin
--标识列
set @bsl =null
set @sql = 'use [TestDataBase] SELECT @a=COLUMN_NAME FROM [TestDataBase].INFORMATION_SCHEMA.columns WHERE TABLE_NAME='''+@tbName+''' AND COLUMNPROPERTY(
OBJECT_ID('''+@tbName+'''),COLUMN_NAME,''IsIdentity'')=1'
exec sp_executesql @sql, N'@a varchar(100) OUTPUT', @bsl OUTPUT
--SELECT @bsl = COLUMN_NAME FROM [TestDataBase].INFORMATION_SCHEMA.columns as a,[TestDataBase].dbo.syscolumns as b
-- WHERE TABLE_NAME=@tbName AND b.name = a.COLUMN_NAME and b.id = (select id from [TestDataBase].dbo.sysobjects where name = @tbName)
-- and b.[status] = 128
if (@bsl is not null)--标识列不为空
begin
set @sql = 'alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' drop column ['+@bsl+']'
exec (@sql)
set @sql = 'use [TestDataBase] SELECT @a = IDENT_SEED ('''+@tbName+''')'
exec sp_executesql @sql, N'@a int OUTPUT', @bszz OUTPUT
set @sql = 'use [TestDataBase] SELECT @a = IDENT_INCR ('''+@tbName+''')'
exec sp_executesql @sql, N'@a int OUTPUT', @bsdz OUTPUT
set @sql = 'Alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' Add ['+@bsl+'] int identity('+@bszz+','+@bsdz+')'
exec (@sql)
end
select @tbCol = stuff((select ','+name from [TestDataBase].dbo.syscolumns where id = (select id from [TestDataBase].dbo.sysobjects where name = @tbName)
FOR XML PATH('')),1,1,'')
if Exists(select * From [TestDataBase].dbo.SYSCOLUMNS as a,[TestDataBase].dbo.sysobjects as b where a.id=b.id and b.name=@tbName and LOWER(a.name)='minename')--表存在矿井名称字段
begin
if (@bsl is not null)--带标识列
begin
set @sql = 'set IDENTITY_INSERT [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' on;
insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName + ' where minename = ''' + @mineName+''''--复制当前矿井数据
+';set IDENTITY_INSERT [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' off'
end
else--不带标识列
begin
set @sql = 'insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName + ' where minename = ''' + @mineName+''''--复制当前矿井数据
end
end
else
begin
if (@bsl is not null)--带标识列
begin
set @sql = 'set IDENTITY_INSERT [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' on;
insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName--复制所有数据
+';set IDENTITY_INSERT [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' off'
end
else--不带标识列
begin
set @sql = 'insert into [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+'('+@tbCol+') select '+@tbCol+' from [TestDataBase].dbo.'+@tbName--复制所有数据
end
end
exec (@sql)
--主键
set @sql = 'alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' add constraint PK_'+@tbName+' primary key('+@keyName+')'
exec (@sql)
end
set @keyName = ''
end try
begin catch
set @keyName = ''
print (@tbName+'表同步失败:'+ERROR_MESSAGE())
set @realNum = @realNum+1
set @failNum = @failNum+1
FETCH NEXT FROM cursor_fkeyCols INTO @tbName
CONTINUE
end catch
set @realNum = @realNum+1
FETCH NEXT FROM cursor_fkeyCols INTO @tbName
END
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
DECLARE @colName varchar(100)
DECLARE @defVal varchar(300)
DECLARE @conName varchar(100)
--获取数据库中带默认值的字段信息（表名、字段名、默认值、约束名）
DECLARE cursor_fkeyCols CURSOR FOR SELECT ST.[name] AS tbName, SC.[name] AS colName, SD.definition AS defVal, SD.[name] AS conName
FROM [TestDataBase].sys.tables ST INNER JOIN [TestDataBase].sys.syscolumns SC ON ST.[object_id] = SC.[id]
INNER JOIN [TestDataBase].sys.default_constraints SD ON ST.[object_id] = SD.[parent_object_id] AND SC.colid = SD.parent_column_id
ORDER BY ST.[name], SC.colid
OPEN cursor_fkeyCols
FETCH NEXT FROM cursor_fkeyCols INTO @tbName,@colName,@defVal,@conName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
set @sql = 'alter table [Platform_'+ @mineDesc+'_v3.0].dbo.'+@tbName+' add constraint '+@conName+' default '+@defVal+' for '+@colName
exec (@sql)
end try
begin catch
FETCH NEXT FROM cursor_fkeyCols INTO @tbName,@colName,@defVal,@conName
CONTINUE
end catch
FETCH NEXT FROM cursor_fkeyCols INTO @tbName,@colName,@defVal,@conName
END
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
print ('表同步完成！成功'+convert(varchar,@allCount-@failNum)+',失败'+convert(varchar,@failNum))
print ('-------------------------------------------------------------------')
update [TestDataBase].dbo.Sys_Mine set CurrentMine = 0 where MineName = @mineName--设置矿井为当前矿井
/*同步视图*/
--set @realNum = 1--当前正在复制第1个视图
--set @failNum = 0--复制失败视图个数
--select @allCount = COUNT(1) FROM [TestDataBase].sys.sql_modules WHERE object_id IN (SELECT object_id FROM [TestDataBase].sys.views )
--DECLARE cursor_fkeyCols CURSOR FOR SELECT definition FROM [TestDataBase].sys.sql_modules WHERE object_id IN (SELECT object_id FROM [TestDataBase].sys.views )
--OPEN cursor_fkeyCols
--FETCH NEXT FROM cursor_fkeyCols INTO @sql
--WHILE @@FETCH_STATUS = 0
-- BEGIN
--BEGIN TRY
-- DECLARE @sql1 NVARCHAR(4000)
-- print('视图 总/当前:'+convert(varchar,@allCount)+','+convert(varchar,@realNum))--复制进度
-- set @sql1 = 'use [Platform_'+ @mineDesc+'_v3.0]'
-- set @sql = replace(@sql,'VIEW dbo.','VIEW ')
-- set @sql = replace(@sql,'dbo.','[Platform_'+ @mineDesc+'_v3.0].dbo.')
-- exec(@sql1 + 'exec (''' + @sql + ''')')
-- end try
-- begin catch
-- print ('同步失败:'+ERROR_MESSAGE())
-- set @realNum = @realNum+1
-- set @failNum = @failNum+1
-- FETCH NEXT FROM cursor_fkeyCols INTO @sql
-- CONTINUE
-- end catch
-- set @realNum = @realNum+1
--FETCH NEXT FROM cursor_fkeyCols INTO @sql
-- END
--CLOSE cursor_fkeyCols
--DEALLOCATE cursor_fkeyCols
--print ('视图同步完成！成功'+convert(varchar,@allCount-@failNum)+',失败'+convert(varchar,@failNum))
print ('-------------------------------------------------------------------')
/*同步存储过程*/
DECLARE @objName varchar(80)
DECLARE @objType varchar(80)
set @realNum = 1--当前正在复制第1个视图
set @failNum = 0--复制失败视图个数
select @allCount = COUNT(1)
FROM [TestDataBase].sys.sql_modules AS sm
JOIN [TestDataBase].sys.objects AS o ON sm.object_id = o.object_id
where definition is not null and is_ms_shipped = 0
DECLARE cursor_fkeyCols CURSOR FOR
SELECT sm.definition,o.name as objName,o.type as objType
FROM [TestDataBase].sys.sql_modules AS sm
JOIN [TestDataBase].sys.objects AS o ON sm.object_id = o.object_id
where definition is not null and is_ms_shipped = 0 order by sm.uses_database_collation desc,o.type,O.create_date
OPEN cursor_fkeyCols
FETCH NEXT FROM cursor_fkeyCols INTO @sql,@objName,@objType
WHILE @@FETCH_STATUS = 0
begin
BEGIN TRY
print('函数、视图和存储过程 总/当前:'+convert(varchar,@allCount)+','+convert(varchar,@realNum))--复制进度
set @sql = replace(@sql,'''','''''')
exec('use [Platform_'+ @mineDesc+'_v3.0] exec (''' + @sql + ''')')
end try
begin catch
if(@objType = 'TF')
begin
print ('表函数 '+@objName+'同步失败:'+ERROR_MESSAGE())
end
else if(@objType = 'FN')
begin
print ('标量函数 '+@objName+'同步失败:'+ERROR_MESSAGE())
end
else if(@objType = 'P')
begin
print ('存储过程'+@objName+'同步失败:'+ERROR_MESSAGE())
end
else if(@objType = 'V')
begin
print ('视图'+@objName+'同步失败:'+ERROR_MESSAGE())
end
else
begin
print (@objName+'同步失败:'+ERROR_MESSAGE())
end
set @realNum = @realNum+1
set @failNum = @failNum+1
FETCH NEXT FROM cursor_fkeyCols INTO @sql,@objName,@objType
CONTINUE
end catch
set @realNum = @realNum+1
FETCH NEXT FROM cursor_fkeyCols INTO @sql,@objName,@objType
end
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
print ('函数、视图和存储过程同步完成！成功'+convert(varchar,@allCount-@failNum)+',失败'+convert(varchar,@failNum))
--备份目标数据库到master所在文件下
set @sql = 'BACKUP DATABASE [Platform_'+ @mineDesc+'_v3.0] TO DISK='''+@path+'Platform_'+ @mineDesc+'_v3.0.bak'' With INIT'
PRINT @sql
EXEC(@sql)
PRINT ('备份数据库成功:'+@path+'Platform_'+ @mineDesc+'_v3.0.bak')
set @sql = 'drop database [Platform_'+@mineDesc+'_v3.0]'--删除目标数据库(先清空正在使用,再删除)
BEGIN TRY
DECLARE sptb CURSOR LOCAL
FOR
SELECT ks = 'kill ' + CAST(spid AS VARCHAR)
FROM MASTER..sysprocesses
WHERE dbid = DB_ID('Platform_'+@mineDesc+'_v3.0')
OPEN sptb
FETCH NEXT FROM sptb INTO @ks
WHILE @@fetch_status = 0
BEGIN
EXEC (@ks)
FETCH NEXT FROM sptb INTO @ks
END
CLOSE sptb
DEALLOCATE sptb
exec (@sql)
print ('删除目标数据库成功')
end try
begin catch
print ('删除目标数据库失败:'+ERROR_MESSAGE())
return
end catch
set @meg = @path+'Platform_'+ @mineDesc+'_v3.0.bak'
return 1
end
else
begin
set @meg = '源数据库中不存在矿井'''+@mineName+''''
print (@meg)
end
end
else
begin
set @meg = '源数据库中不存在表''Sys_Mine'''
print (@meg)
end
end
else
begin
set @meg = '源服务器中不存在数据库''TestDataBase'''
print (@meg)
end
return 0
end
END
