alter schema on table causes a mismatch

geoffhoward

I am running a simple owner change, remapping to a new security schema.

alter schema [NewSchema] transfer dbo.[MyTable]
go

When I run SQL Compare 7.1 or 8 my table does not show up with a change, rather it shows up as two different tables between databases. This is not desirable as it will not preserve my data. Am I missing and option or is this a shortfall of SQL Compare?

David Atkinson

This "Smart Rename" feature is in SQL Refactor, which is a separate product. However, you can download the tool and try out the feature.

http://www.red-gate.com/products/sql_refactor/index.htm

Let us know how you get on.

David Atkinson
Red Gate Software

geoffhoward

So you are saying the only way for SQL Compare to acknowledge a change of schema (owner) on a table object without detecting them as two entirely different objects is to use SQL Refactor? That would seem like a very limiting "feature" for SQL Compare. How unfortunate and short-sighted.

David Atkinson

Thanks for your comments and we're sorry that the tool doesn't meet your expectations. To my knowledge there is nothing stored in the database itself that contains historical object renaming information and manual object name mapping isn't one of SQL Compare's features, although it is a candidate for a future release.

Did SQL Refactor not work for you?

David

geoffhoward

Thanks for the quick reply David. SQL Compare is a great product which I have been using for over a year. This is the first hiccup I have had with it, but I must say it is a big one. I actually mostly use the SDK and wrote a wrapper application around it so SQL Refactor is not really a viable option.

As far as the renaming goes, I am not renaming the table itself, I am simply assigning it to a different security schema.

select * from sys.all_objects where type = 'U' and name = 'MyTable'

this contains a schema_id which can be located here:

select * from sys.schemas

The object_id within sys.all_objects remains the same throughout the life-cycle of the schema transfer.

David Atkinson

If you edit your SQL Compare project, you'll see that there is a tab called 'Owner mapping'. If your object names are the same this might allow you to compare differing schemas together.

Worth a try.

If this doesn't work, please let us know!

David