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

原创 2005年08月20日 08:14:00

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

-- 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

     原帖地址

使用更改跟踪实现数据同步

 SQL Server 2008 引入了更改跟踪,这是一种轻量型解决方案,它为应用程序提供了一种有效的更改跟踪机制。通常,若要使应用程序能够查询对数据库中的数据所做的更改和访问与这些更改相关的信息,应...
  • zjcxc
  • zjcxc
  • 2009年02月23日 06:59
  • 8740

【通用】获取表结构、索引、主外键的存储过程

非常实用的SQL,希望大家喜欢。   ---------------Table2012/7/30 19:29:05--------------------- use master go ...
  • t134679
  • t134679
  • 2012年07月31日 23:47
  • 920

sqlserver表结构(含约束)复制存储过程

SQL Server 表结构(含约束)复制存储过程

数据库复制:修改表结构、新增表、新增存储过程 会被复制到订阅服务器

(http://www.cnblogs.com/happyday56/p/3849018.html) SQL Server高可用性】数据库复制:修改表结构、新增表、新增存储过程 会被...

【SQL Server高可用性】数据库复制:修改表结构、新增表、新增存储过程 会被复制到订阅服务器?

在SQL Server上配置好数据库复制后:       1、如果给表加了字段,会不会复制到订阅服务器呢?如果是删除1个字段呢?       2、如果新创建了1个表,会不会被同步过去呢?       ...
  • yupeigu
  • yupeigu
  • 2014年03月06日 21:23
  • 2081
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:复制表结构的通用存储过程
举报原因:
原因补充:

(最多只允许输入30个字)