Creating single db from multiple dbs-Owner mapping issue
mryhmln
Posts: 16
I want to use SQL Compare to compare stored procedure, function, and view objects from several existing databases against those in an empty database, with the intent of consolidating multiple databases into a single database with multiple schemas. In the source databases, the objects are all in the dbo schema, but in the target database, the objects will be in the schema having the same name as the source database, e.g. DB1, DB2, etc. The issue I am having is with the owner mapping. When I run the comparison, I set the owner mapping of the dbo schema of the source database to the corresponding schema in the target database, e.g. DB1, because I want SQL Compare to create all of the new objects in the DB1 schema of the target database, not the dbo schema. This works as expected. However, in many cases I have script code inside of my objects that reference some of the other databases by name, e.g. "DB2.dbo.OBJECTNAME", which seem to be changed automatically by SQL Compare to "DB1.OBJECTNAME", which is not what I want. Instead, I either want SQL Compare to leave the external database references alone, or ideally I would want it to automatically map them to the new schema that I created with the same name as the external database, e.g. DB2.OBJECTNAME. Is this possible? And if not, what would be the best way to accomplish what I am trying to do, i.e. creating a single database with multiple schemas from multiple source databases? Any help would be greatly appreciated. Thanks
Comments
I've had a quick test of the scenario you describe and I seem to get the same thing. I'm not sure if my test is exactly the same process as you have, but I seem to get a similar result, in that as well as changing the schema on the object itself (a sproc in my case, and which I assume is correct) we seem to change it on any references inside the code of the procedure.
I wondered if synonyms would help, so I set one up to point to a table in another database, and changed my proc to do a "select from <synonym>" but Compare still bolts the new schema name on the front of that, which is odd, as there's nothing for it to actually want to replace there. Of course this is slightly less horrible, because you can have the synonym in the new database and still have it point to the correct external reference. The only problem is how much work it will be for you to change to using those...
At this point I'm not sure if what you're seeing is a bug or just intended behaviour and you have a slightly odd scenario, so I've asked the Compare team for their thoughts. If it's a bug then of course we'll raise a fix request for that, although I'm not sure as to a timescale for a fix should that happen.
Redgate Software