Stored Procedure to transfer all DB objects including data from one schema to another schema

  • hi,

    Stored Procedure to transfer all DB objects including data from one schema to another schema

    this is the code i tried

    ALTER PROC sp_schema_to_schema

    @sourceSchema nvarchar(100),

    @targetSchema nvarchar(100)

    AS

    if not exists(select database_id from sys.databases where name =@targetschema)

    begin

    exec('Create DATABASE'+' '+ @targetschema)

    begin

    exec('USE'+' '+ @targetschema)

    DECLARE c_ALTSCHEMA CURSOR FOR

    --SELECT 'ALTER SCHEMA ' + @targetSchema + ' TRANSFER ' + @sourceSchema + '.'+name +';'

    --FROM sys.objects

    --WHERE type IN ('U','V','P','Fn')

    --AND SCHEMA_NAME(SCHEMA_ID) = @sourceSchema

    SELECT 'ALTER SCHEMA ' + @targetSchema + ' TRANSFER ' + @sourceSchema + '.'+o.name +';'

    FROM sys.objects o

    INNER JOIN sys.Schemas s ON o.schema_id = s.schema_id

    WHERE s.Name = @sourceSchema

    AND (o.Type IN ('U', 'P', 'V'))

    DECLARE @SQLStmt NVARCHAR(200)

    OPEN c_ALTSCHEMA

    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC(@SQLStmt)

    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt

    END

    CLOSE c_ALTSCHEMA

    DEALLOCATE c_ALTSCHEMA

    END

    END

    EXEC sp_schema_to_schema AdventureWorks2014, AdventureWorks2019

    problem is this only creates the Database named not the tables,data,procedure or view, please help me in this to find the solution

  • First a question, can you explain why you want to do this?

    😎

    There are several ways of doing this, some good and some bad, unfortunately the T-SQL ones are not the best.

  • problem, the script only creates the database, it deos not copying the table,data and SP's, please suggest some code changes alternate way to do this.

  • GA_SQL (12/7/2016)


    problem, the script only creates the database, it deos not copying the table,data and SP's, please suggest some code changes alternate way to do this.

    ALTER SCHEMA schema_name TRANSFER transfers a securable between schemas in the current database, it will not copy data between databases.

    😎

  • Anyways to copy the DB objects with data from one schema to another using stored procedure.

  • GA_SQL (12/7/2016)


    Anyways to copy the DB objects with data from one schema to another using stored procedure.

    You will need to script out all objects in the source db, then create them in the destination db and finally copy the data. Depending on the complexity etc. this can either be easy or rather painful when using T-SQL. Do you have any other options (id. PoSh, SSIS etc.)?

    😎

  • First, your USE statement only changes the database context for the batch in which it's executed, which, in your case, is only the USE statement itself.

    Second, creating a database called NewDB doesn't automatically create a schema called NewDB, so there'll be no new schema to transfer the objects to.

    Third, since it's a newly created database, there are no objects to transfer to a different schema.

    As Eirikur said, what exactly are you trying to do? It looks as if you are trying to move objects from one database to another, and that the schema is something of a red herring. If we all take a step back from it, we may be able to see a better way of doing it.

    John

  • Yes i am trying to move all the DB objects from one schema to another schema using SP, using BCP will be right option??

  • So you have a schema called AdventureWorks2014 that has objects in it, and you have an empty schema called AdventureWorks2019 that you want to move those objects to. Is that right? What database(s) are those schemas in?

    John

  • If you are either copying all or majority of the objects, I would suggest doing a backup and restore with a different name, then drop anything that is not needed.

    😎

  • My objective is to move the contents of one db to another db

  • OK, so the schema thing is a red herring. Do you want to move all objects? Do you want to move data, or just create empty tables? Does the target database already exist? Do you want permissions in the target database to be the same as in the source? Sounds like a simple backup and restore might be your best option.

    John

  • so backup and restore SP will do

  • GA_SQL (12/7/2016)


    My objective is to move the contents of one db to another db

    You can easily use the SSMS Import and Export Wizard for this, and then if it needs to be repeated, choose the option to save process as a SSIS package which you can run on demand.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • i got the requirement exactly.. i have schema called dbo.table1,dbo.table2 that has to be copied (not transfer) dbonew.table1,dbonew.table2 (with in same db), this below script transfers the name since dbo has been replaced to dbo1, please help me in copy one schema to another schema with in same DB

    create PROC sp_schema_to_schema_new

    --@sourcedb nvarchar(100),

    @sourceSchema nvarchar(100),

    @targetSchema nvarchar(100)

    AS

    if not exists(select database_id from sys.databases where name =@targetschema)

    --begin

    --exec('USE'+' '+ @sourcedb)

    begin

    exec('Create SCHEMA'+' '+ @targetschema)

    DECLARE c_ALTSCHEMA CURSOR FOR

    SELECT 'ALTER SCHEMA ' + @targetSchema + ' COPY'+ @sourceSchema + '.'+name +';'

    FROM sys.objects

    WHERE type IN ('U','V','P','Fn')

    AND SCHEMA_NAME(SCHEMA_ID) = @sourceSchema

    DECLARE @SQLStmt NVARCHAR(200)

    OPEN c_ALTSCHEMA

    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC(@SQLStmt)

    FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt

    END

    CLOSE c_ALTSCHEMA

    DEALLOCATE c_ALTSCHEMA

    END

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply