alter schema on table causes a mismatch

geoffhowardgeoffhoward Posts: 4
edited July 15, 2010 1:00PM in SQL Compare Previous Versions
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?

Comments

  • 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
    David Atkinson
    Product Manager
    Redgate Software
  • 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.
  • 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
    David Atkinson
    Product Manager
    Redgate Software
  • 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.
  • 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
    David Atkinson
    Product Manager
    Redgate Software
Sign In or Register to comment.