复制表结构的通用存储过程

核心提示：-- Transfer对象的重要属性 -- 1. 属性 属性名类型描述- --- CopyAllDefaultsBoolean所有默认值CopyAllObjects Boolean所有对象CopyAllRules Boolean所有规则CopyAllStoredProceduresBoolean所有存储过程CopyAl

-- Transfer对象的重要属性

-- 1. 属性

属性名　　　　　　　　　　　　　　类型　　　　　　　　描述
--------------------------------- ------------------- --------------------
CopyAllDefaults　　　　　　　　Boolean　　所有默认值
CopyAllObjects　　　　　　　　 Boolean　　所有对象
CopyAllRules　　　　　　　　　 Boolean　　所有规则
CopyAllStoredProcedures　　　　Boolean　　所有存储过程
CopyAllTables　　　　　　　　　Boolean　　所有表
CopyAllTriggers　　　　　　　　Boolean　　所有触发器
CopyAllUserDefinedDatatypes　　Boolean　　所有用户自定义类型
CopyAllViews　　　　　　　　　 Boolean　　所有视图
CopyData　　　　　　　　　　　 Boolean　　所有数据
DestDatabase　　　　　　　　　 String　　 目标对象数据库
DestLogin　　　　　　　　　　　String　　 目标数据库登陆用户名
DestPassword　　　　　　　　　 String　　 目标数据库登陆密码
DestServer　　　　　　　　　　 String　　 目标服务器
DestUseTrustedConnection　　　 Boolean　　用户信任连接
DropDestObjectsFirst　　　　　 Boolean　　是否先删除目标对象
IncludeDependencies　　　　　　Boolean　　是否包含依靠对象
ScriptType　　　　　　　　　　 Boolean　　脚本类型

-- 2. 重要方法: 

方法名称　　　　　　　　　　功能描述
--------------------------- --------------------------
AddObject　　　　　　　　　 增加对象
AddObjectByName　　　　　　 通过对象名称增加对象

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_CopyDB]
GO

/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移
　　存储过程实现源数据库到目标数据库的对象和数据的复制
　　要求源数据库和目标数据库在同一服务器
　　如果是要实现不同服务器之间的复制，则需要增加验证信息
--邹建 2005.07(引用请保留此信息)--*/

/*--调用示例

　　CREATE DATABASE test
　　EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test'
　　DROP DATABASE test
--*/
CREATE PROCEDURE P_CopyDB　　 
@Des_DB　　　sysname,　　　　　 --目标数据库
@Obj_Type　　nvarchar(4000)=N'',--复制的对象类型，可以是下列字符串列表：
　　　　　　　　　　　　　　　　-- O 所有对象，D 默认值，R 规则，P 存储过程
　　　　　　　　　　　　　　　　-- T 表，TR 触发器，DT 用户定义数据类型
　　　　　　　　　　　　　　　　-- V 视图，DATA 数据，DEL 删除目标对象
@Source_DB　 sysname=N'',　　　 --源数据库
@ServerName　sysname=N'',　　　 --服务器名
@UserName　　sysname=N'',　　　 --用户名，不指定则表示使用 Windows 身份登录
@pwd　　　　 sysname=N''　　　　--密码 
AS
SET NOCOUNT ON
DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int,
　　@err int,@src varchar(255), @desc varchar(255)

IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME
IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME()

--创建sqldmo对象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err

--连接服务器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
　　EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
　　IF @err<>0 GOTO lb_Err

　　EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
END
ELSE
　　EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwd

IF @err<>0 GOTO lb_Err

--获取数据库集
EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
IF @err<>0 GOTO lb_Err

--选择源数据库　　
EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
IF @err<>0 GOTO lb_Err

--选择目标数据库　　
EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
IF @err<>0 GOTO lb_Err

--设置复制的对象
EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
IF @err<>0 GOTO lb_Err

--设置目标服务器信息
EXEC @err=sp_oasetproperty　@TransferID,'DestServer',@ServerName
IF @err<>0 GOTO lb_Err

　--设置连接用户
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
　　EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
　　IF @err<>0 GOTO lb_Err
END
ELSE
BEGIN
　　EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
　　IF @err<>0 GOTO lb_Err

　　EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
　　IF @err<>0 GOTO lb_Err
END

　--设置复制对象信息
EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
IF @err<>0 GOTO lb_Err

DECLARE tb CURSOR FAST_FORWARD LOCAL
FOR
SELECT Name FROM(
　　SELECT KeyWord=N',D,',　 Name=N'CopyAllDefaults' UNION ALL
　　SELECT KeyWord=N',O,',　 Name=N'CopyAllObjects' UNION ALL
　　SELECT KeyWord=N',R,',　 Name=N'CopyAllRules' UNION ALL
　　SELECT KeyWord=N',P,',　 Name=N'CopyAllStoredProcedures' UNION ALL
　　SELECT KeyWord=N',T,',　 Name=N'CopyAllTables' UNION ALL
　　SELECT KeyWord=N',TR,',　Name=N'CopyAllTriggers' UNION ALL
　　SELECT KeyWord=N',DT,',　Name=N'CopyAllUserDefinedDatatypes' UNION ALL
　　SELECT KeyWord=N',V,',　 Name=N'CopyAllViews' UNION ALL
　　SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL
　　SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst'
)A WHERE CHARINDEX(KeyWord,
　　　　CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0
OPEN tb
FETCH tb INTO @src
WHILE @@FETCH_STATUS=0
BEGIN
　　EXEC @err=sp_oasetproperty @TransferID,@src,1
　　IF @err<>0 GOTO lb_Err
　　FETCH tb INTO @src
END
CLOSE tb
DEALLOCATE tb

--复制对象
EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
IF @err<>0 GOTO lb_Err

--结束
SET @err=0
GOTO lb_Exit

--错误处理
lb_Err:
　　EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT 
　　RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc)
　　RETURN -1

lb_Exit:
　　EXEC sp_OADestroy @Dbid　
　　EXEC sp_OADestroy @srvid 
　　EXEC sp_OADestroy @TransferID 
　　RETURN @err
GO