"Duplicates" when remapping owners

emmar00

I'm trying to compare tables in two different schemas from the same database and server. There could be columns that are renamed or missing, or even tables that don't exist on one side or the other. I figured out how to map the schemas (https://documentation.red-gate.com/sc/setting-up-the-comparison/mapping-owners), but I'm basically getting duplicate results since I only want the comparison one way.

I have test_schema mapped to new_schema. I also have new_schema mapped to test_schema because I have to have it mapped to something or I get an error.

This is what I get. As an example, there is one table in each schema that is not in the other. These are shown twice, as are all the other similarities/differences:


And this is what I'm looking for. Only test_schema on the left and only new_schema on the right.


Filters don't seem to work (ex: schema name = test_schema) because it applies to both sides. I also tried mapping new_schema to sys and sys to test_schema. That reduced some of the results, but created more items in the "only in left side/right side" lists.

Dan_J

Hi @emmar00

Thanks for your patience with us on this.

I've spoken with the team here about this again and, whilst it is possible to map schemas in this way in our Oracle product, it isn't currently possible to achieve this within our SQL Compare tool. 

Changing the tool to enable this would be a fundamental change and so this isn't something the team are able to facilitate at this time. I do appreciate this would be useful functionality for you and so encourage you to log a feature request on our SQL Compare UserVoice page: https://redgate.uservoice.com/forums/141379-sql-compare. That way our development team will have visibility of it, and other customer can vote for it also.

emmar00

I've learned that SQL Compare for Oracle can do this - https://www.red-gate.com/products/schema-compare-for-oracle/compare-two-oracle-schemas . Isn't there a way to do this for SQL Server with the regular SQL Compare?

Dan_J

Hi @emmar00

Thanks for reaching out to us regarding this.

In order to achieve what you are after here we recommend the following steps:

• Create a new database (which is a copy of the source database - this can be done by restoring a backup file or using SQL Compare to deploy to a new empty database)
• Then, setup a SQL Compare project to compare the db-dbatest19 EGR_Playground to the new copy of the same database
• Then, navigate to the owner mapping tab and unmap the test_schema and new_schema from one another
• Then, map the test_schema in the source to the new_schema in the target (leaving new_schema unmapped in the source and test_schema unmapped in the target)

I hope this helps!

emmar00

@Dan_J

That doesn't work, because you can't leave schemas unmapped, apparently. I created a new database and used SQL Compare to copy over the structures. I set up the comparison like you said:


And on running the compare:


If I map them back together, then the comparison will go through, but that just brings me back to square one.

Dan_J

Hi @emmar00

My apology for the delay in coming back to you on this.

So, it should be possible to run a comparison whilst there is an owner left unmapped, so I'm not 100% sure why you're seeing the error you are. 

I've also tried to reproduce this issue with the scenario you are describing but am so far not seeing the same issue. 

I wondered, would it be possible for you to enable verbose logging (https://documentation.red-gate.com/sdc/troubleshooting/logging-and-log-files#:~:text=Verbose logging reports all messages,recommended to select No Logging.), recreate the issue and provide the resulting log file? This may help to highlight exactly where the issue is.

emmar00

@Dan_J Interesting that it works for you. My understanding based on the documentation page (linked in my original post) was that it shouldn't work at all - it's pretty explicit about that in the 3rd bullet point.

Nevertheless, I turned on logging this morning and got the error captured. Log file is attached.

Dan_J

Hi @emmar00

My Sincere apology for the delay in coming back to you on this.

After much further testing on this, the bullet point you highlighted in our documentation is still correct. A test comparison here errored in the same way you are seeing.

As you previously mentioned, it is possible to achieve what you are after within Schema Compare for Oracle because the product is designed to primarily compare schemas. Regarding SQL Compare, so far I've not found a way to achieve what you are after. There is also discussion on-going as to whether there might be a bug in the software in relation to comparing tables in different schema's in the way you describe. 

I'm continuing to work with the team here to see if we can work around this issue. I'll come back to you on this again as soon as I have an update for you.

emmar00

I did file a feature request. I was eventually able to find a workaround similar to the suggestion from Nov 30 and thought I would share:

1. Create a new database

2. Use SQL Compare to copy over:
        a. Source schema (in above discussion, this would be test_schema)
        b. Target schema (new_schema)
        c. Objects on target schema

Tip: exclude users and roles first to eliminate clutter, then filter for schema name = target.

3. Move objects to the other schema

USE YourNewDB
GO

SELECT 'ALTER SCHEMA <source> TRANSFER ' + s.name + '.' + o.name

FROM sys.objects o

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

WHERE s.name = '<target>'

AND o.type IN ('U', 'V', 'P') -- tables, views, procedures, expand list as needed

Copy output and execute

4. Go back to SQL Compare, clear any filters or selected items, and refresh the comparison. Now you have the comparison report.

If you want to deploy the changes across, probably the easiest way would be to generate the comparison script, save it, replace the schema name, and then execute it on the original database.