复制表结构的通用数据库存储过程
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对象· dedecms.com 本文来自织梦
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
文章评论
共有位Admini5网友发表了评论 查看完整内容