Copy from one database to another and change schema
I am looking to merge the contents of the two databases. In both both databases, all objects belong to the dbo schema.
What I am looking to do it to move all objects and data from my db1 database into my db2 database, but instead of the objects being part of the dbo schema, I want all the objects to belong to a new schema. e.g cms
Looking at sql compare project options, as the objects don't already exist I cannot do any mapping.
Is there an easy way using SQL Compare and SQL Data Compare to do what I wish to do, or is my best bet to just use a tool to generate the create object script and then do a search and replace on the text replacing all instanced of [dbo] with [cms]?
And then once these objects have been created do a mapping exercise.