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

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

--Transfer对象的重要属性

--1.属性

属性名类型描述
------------------------------------------------------------------------
CopyAllDefaultsBoolean所有默认值
CopyAllObjectsBoolean所有对象
CopyAllRulesBoolean所有规则
CopyAllStoredProceduresBoolean所有存储过程
CopyAllTablesBoolean所有表
CopyAllTriggersBoolean所有触发器
CopyAllUserDefinedDatatypesBoolean所有用户自定义类型
CopyAllViewsBoolean所有视图
CopyDataBoolean所有数据
DestDatabaseString目标对象数据库
DestLoginString目标数据库登陆用户名
DestPasswordString目标数据库登陆密码
DestServerString目标服务器
DestUseTrustedConnectionBoolean用户信任连接
DropDestObjectsFirstBoolean是否先删除目标对象
IncludeDependenciesBoolean是否包含依靠对象
ScriptTypeBoolean脚本类型

--2.重要方法:

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

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[P_CopyDB]')andOBJECTPROPERTY(id,N'IsProcedure')=1)
dropprocedure[dbo].[P_CopyDB]
GO

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

/*--调用示例

CREATEDATABASEtest
EXECP_CopyDB@Source_DB='northwind',@Des_DB='test'
DROPDATABASEtest
--*/
CREATEPROCEDUREP_CopyDB
@Des_DBsysname,--目标数据库
@Obj_Typenvarchar(4000)=N'',--复制的对象类型，可以是下列字符串列表：
--O所有对象，D默认值，R规则，P存储过程
--T表，TR触发器，DT用户定义数据类型
--V视图，DATA数据，DEL删除目标对象
@Source_DBsysname=N'',--源数据库
@ServerNamesysname=N'',--服务器名
@UserNamesysname=N'',--用户名，不指定则表示使用Windows身份登录
@pwdsysname=N''--密码
AS
SETNOCOUNTON
DECLARE@srvidint,@Dbidint,@S_dbidint,@D_dbidint,@TransferIDint,
@errint,@srcvarchar(255),@descvarchar(255)

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

--创建sqldmo对象·
EXEC@err=sp_oacreate'sqldmo.sqlserver',@srvidOUT
IF@err<>0GOTOlb_Err

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

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

IF@err<>0GOTOlb_Err

--获取数据库集
EXEC@err=sp_oagetproperty@srvid,'databases',@DbidOUT
IF@err<>0GOTOlb_Err

--选择源数据库
EXEC@err=sp_oamethod@Dbid,'item',@S_dbidOUT,@Source_DB
IF@err<>0GOTOlb_Err

--选择目标数据库
EXEC@err=sp_oamethod@Dbid,'item',@D_dbidOUT,@Des_DB
IF@err<>0GOTOlb_Err

--设置复制的对象
EXEC@err=sp_oacreate'SQLDMO.Transfer',@TransferIDOUT
IF@err<>0GOTOlb_Err

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

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

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

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

DECLAREtbCURSORFAST_FORWARDLOCAL
FOR
SELECTNameFROM(
SELECTKeyWord=N',D,',Name=N'CopyAllDefaults'UNIONALL
SELECTKeyWord=N',O,',Name=N'CopyAllObjects'UNIONALL
SELECTKeyWord=N',R,',Name=N'CopyAllRules'UNIONALL
SELECTKeyWord=N',P,',Name=N'CopyAllStoredProcedures'UNIONALL
SELECTKeyWord=N',T,',Name=N'CopyAllTables'UNIONALL
SELECTKeyWord=N',TR,',Name=N'CopyAllTriggers'UNIONALL
SELECTKeyWord=N',DT,',Name=N'CopyAllUserDefinedDatatypes'UNIONALL
SELECTKeyWord=N',V,',Name=N'CopyAllViews'UNIONALL
SELECTKeyWord=N',DATA,',Name=N'CopyData'UNIONALL
SELECTKeyWord=N',DEL,',Name=N'DropDestObjectsFirst'
)AWHERECHARINDEX(KeyWord,
CASEWHENISNULL(@Obj_Type,N'')=''THEN',O,DATA,'ELSE@Obj_TypeEND)>0
OPENtb
FETCHtbINTO@src
WHILE@@FETCH_STATUS=0
BEGIN
EXEC@err=sp_oasetproperty@TransferID,@src,1
IF@err<>0GOTOlb_Err
FETCHtbINTO@src
END
CLOSEtb
DEALLOCATEtb

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

--结束
SET@err=0
GOTOlb_Exit

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

lb_Exit:
EXECsp_OADestroy@Dbid
EXECsp_OADestroy@srvid
EXECsp_OADestroy@TransferID
RETURN@err
GO

原帖地址