Moving Database Objects from One Schema to Different Schemas

Database development is not limited to an initial copy of a database. On your road you meet multiple enhancements, refactoring, upgrade and many other cases that require changes in a database structure. One of most common cases is to upload database objects from one schema to several schemas. Here the simplicity ends and you are stuck with the questing ‘How to do this?’

Upload manually or automatically?

Of course you can do this manually. In this case you should arm yourself with much time. And if your database has many objects, not to speak of multiple dependencies between them, you’d better find a decent tool to find your way out and complete the task successfully.  How to select a tool? The schema comparison tool must provide custom mapping of schemas, otherwise you will need robot skills and patience to repeat the same operation ten times or more.

SQL Compare tool from Devart, is prepared to upload database objects from the same schema to several schemas and can do it with any databases.

Let us examine how it helps. To illustrate the case, take a database called ‘development’ with two tables – ‘dept’ and ‘emp’, where ‘emp’ relates to ‘dept’ by using a foreign key constraint. The tables are in the same ‘dbo’ schema. The task is to move the ‘dept’ table to the ‘department’ schema and the ‘emp’ table to the ‘employee’ schema.

How dbForge Schema Compare for SQL Server can help

Steps to do this with schema comparison tool from Devart include:

1. 1. Create ‘department’ and ‘employee’ schemas in the database ‘development’ if no exist.
   2. Open SQL comparison wizard and select the ‘production’ database as Source, and the ‘development’ database as Target.

      

   3. Move to the Schema Mapping wizard page. Here we need to map the ‘dbo’ schema (the one with two tables) with ‘department’ and ‘employee’ schemas to move the tables. Click the fields in the Target column and select required schemas from the list.

1. 1. Click Compare to compare databases with custom mapping. We get the comparison results neatly displayed in the grid. The objects that exist only in the source database are highlighted in light gray.  Now we should select required objects to create in the target database. Select the check boxes next to the first and the last object in this group to create them in  ‘department’ and ‘employee’ schemas.

Note: As we do not need to duplicate the objects in each schema, we select only required objects, not all in the group.

• The task is almost complete. It takes only to generate a synchronization script by using the Synchronize (F8) command. As the result the aforementioned tables will be created in new schemas, not in the ‘dbo’ schema.

dbForge Schema Compare for SQL Server not only replaced schema names in tables, but tracked where each table would be created and replaced schema names in object references. In our case, the schema name was replaced in the foreign key.

SQL schema compare tool by Devart is a good help in completing tough database migration tasks.

Thanks to it, we did not care about objects DDL, as it completely generated it. The SQL comparison tool saved us much time and effort as it tracked all the object dependencies and replaced them accordingly. The very task we can consider is to set up comparison.

Get your free 30-day trial of dbForge Schema Compare for SQL Server. Try and decide.