Brian Donahue wrote:Hello,
Sometimes an index needs to be dropped in order to facilitate a change to the underlying column. For instance if the column datatype changes or something, you can't alter the table to drop the column until the index based on the column is dropped.
Hi Brian thanks for the reply,
I understand that can sometimes be the case and other objects might need to be re-created depending on the change required.
In the situation I am seeing though, I am able to write drop constraint/create constraints individually without requiring any other modifications to the tables or indexes.
So for a number of tables where I have needed to sync a large number of differences with column constraints I have had to generate the change script then go through and manually edit it to remove all the index drop/creates (and sometimes even a full table rebuild with copy to tmp table and rename) before running it on the target server.
This has been a real pain lately since applying these unnecessary steps cannot be allowed on a production box where the affected tables sometimes have millions of rows, and it's entirely possible to make the required changes without a table or index rebuild - particularly as there are no other differences between the tables other than the constraints!