sql-server - 在注册期间尝试将完整的表复制到另一个架构中

Question

我正在开发一个多租户应用程序我计划将表从默认模式（即 dbo）转移到另一个模式，我正在使用存储的 Proc 创建新模式，所有关系都在 dbo 中维护

USE [STAGING]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Dheeraj Singh
-- Create date: getdate
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[-old-sp_transfer_schema_on_signup]
-- Add the parameters for the stored procedure here
@NewSchema varchar(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
    DECLARE @id INT
    DECLARE @name NVARCHAR(100)
    DECLARE @getid CURSOR

    BEGIN TRY
  BEGIN TRANSACTION SCHEDULEDELETE
  SET @getid = CURSOR FOR select name from sys.Objects where sys.Objects.schema_id=1 and type in     ('U','V')
    OPEN  @getid
    WHILE 1=1
    BEGIN
        FETCH NEXT
        FROM @getid INTO @name
        IF @@FETCH_STATUS < 0 BREAK
        
        exec( 'SELECT * INTO [' + @NewSchema + '].[' + @name + '] FROM dbo.[' + @name+ ']')
        --EXEC stored_proc @varName=@id, @otherVarName='test', @varForName=@name
    END
    CLOSE @getid
    DEALLOCATE @getid
     COMMIT TRANSACTION SCHEDULEDELETE
    PRINT 'X rows deleted. Operation Successful Tara.' --calculation cut out.
    END TRY

    BEGIN CATCH 
      IF (@@TRANCOUNT > 0)
       BEGIN
      ROLLBACK TRANSACTION SCHEDULEDELETE
      PRINT 'Error detected, all changes reversed'
   END 
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage
END CATCH

    
END

我所期望的是将所有表从 dbo 复制到新创建的模式，但是在此传输过程中它无法复制关系和键

标签： sql-serversql-server-2008