241.在两个SQL Server数据库之间复制对象

素颜马尾好姑娘i 2023-12-27 06:42 43阅读 0赞

CREATE PROC sp_CopyObject
    @S_DatabaseName sysname,             --要复制对象的源数据库名
    @D_DatabaseName sysname=NULL,      --复制对象的目标数据库名,不指定则为当前库名
    @TypeList        nvarchar(50)=NULL   --以逗号分隔的对象类型列表,只能是P/V/TR/IF/TF/FN之一或者是它们的组合.不指定是所有对象
    AS
    IF DB_ID(@S_DatabaseName) IS NULL
    BEGIN
    	RAISERROR('源数据库 "%s" 不存在',12,16,@S_DatabaseName)
    	RETURN
    END
    IF DB_ID(@D_DatabaseName) IS NULL SET @D_DatabaseName=DB_NAME()
    
    DECLARE @sql nvarchar(4000)
    --处理对象类型列表
    SET @sql=N''
    SELECT @sql=@sql+N','+QUOTENAME(xtype,N'''')
    FROM(
    	SELECT xtype=N'P'  UNION ALL SELECT N'IF' UNION ALL
    	SELECT xtype=N'V'  UNION ALL SELECT N'TF' UNION ALL
    	SELECT xtype=N'TR' UNION ALL SELECT N'FN'
    )a WHERE CHARINDEX(N','+xtype+N',',N','+@TypeList+N',')>0
    IF @@ROWCOUNT=0 OR @sql=''
    	SET @TypeList='N''P'',N''V'',N''TR'',N''IF'',''TF'',''FN'''
    ELSE
    	SET @TypeList=STUFF(@sql,1,1,N'')
    
    --获取要复制对象(存储,视图,触发器)的数据
    CREATE TABLE #(name sysname,xtype char(2),number smallint,colid smallint,text nvarchar(4000))
    SET @sql=N'
    INSERT #(name,xtype,number,colid,text)
    SELECT o.name,o.xtype,m.number,m.colid,m.text
    FROM '+QUOTENAME(@S_DatabaseName)+N'.dbo.sysobjects o,'
    	+QUOTENAME(@S_DatabaseName)+N'.dbo.syscomments m
    WHERE o.id=m.id
    	AND m.encrypted=0
    	AND o.xtype IN('+@TypeList+N')
    	AND o.status>=0'
    EXEC sp_executesql @sql
    
    DECLARE tb CURSOR LOCAL
    FOR
    SELECT N'USE '+QUOTENAME(@D_DatabaseName)+N'
    	DECLARE @name sysname,@number smallint
    	SELECT @name='+QUOTENAME(name,N'''')+N',
    		@number='+CAST(number as varchar)+N'
    	DECLARE @1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(1000)
    	SELECT @1=N'''',@2=N'''',@3=N''''
    	SELECT @1=@1+N'',@''+RTRIM(colid)+N'' nvarchar(4000)'',
    		@2=@2+N'',@''+RTRIM(colid)
    			+N''=CASE colid WHEN ''+RTRIM(colid)
    			+N'' THEN text ELSE @''+RTRIM(colid)+N'' END'',
    		@3=@3+N''+@''+RTRIM(colid)	
    	FROM #
    	WHERE name=@name AND number=@number
    	ORDER BY colid
    	SELECT @1=STUFF(@1,1,1,N''''),
    		@2=STUFF(@2,1,1,N''''),
    		@3=STUFF(@3,1,1,N''''),
    		@4=N'' WHERE name=''+QUOTENAME(@name,N'''''''')
    			+N'' AND number=''+RTRIM(@number)
    	IF @NUMBER<2 AND EXISTS(SELECT * FROM sysobjects WHERE name=@name AND xtype='
    	+QUOTENAME(xtype,N'''')+N') DROP '+CASE xtype
    		WHEN N'P' THEN N'PROC'
    		WHEN N'V' THEN N'VIEW'
    		WHEN N'TR' THEN N'TRIGGER'
    		ELSE N'FUNCTION' END
    	+N' '+QUOTENAME(name)+N'
    	EXEC(N''DECLARE ''+@1+''
    	SELECT ''+@2+'' FROM # ''+@4+N''
    	EXEC(''+@3+'')'')'
    FROM #
    GROUP BY name,xtype,number
    OPEN tb
    FETCH tb INTO @sql
    WHILE @@FETCH_STATUS=0
    BEGIN
    	EXEC sp_executesql @sql
    	FETCH tb INTO @sql
    END
    CLOSE tb
    DEALLOCATE tb