alter schema on table causes a mismatch
geoffhoward
Posts: 4
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?
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
http://www.red-gate.com/products/sql_refactor/index.htm
Let us know how you get on.
David Atkinson
Red Gate Software
Product Manager
Redgate Software
Did SQL Refactor not work for you?
David
Product Manager
Redgate Software
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.
Worth a try.
If this doesn't work, please let us know!
David
Product Manager
Redgate Software