While testing the schema compare we produced a scenario where some columns were changed (i.e. data type, constraint added/modified). Regardless of what we did the “Generate Script” from the Schema compare generated a DROP TABLE statement. Followed by a CREATE TABLE statement. This is unusable/unacceptable in Production environments. I recall when I used Toad for Oracle they handled this differently. Additionally, Microsoft’s SSDT utility also handles this differently.
ANYTIME a table needs dropped, at least, the following should happen:
CREATE A TRANSACTION
Create new temp table based on source definition (include all constraints, FKs, indexes, etc)
Insert data from production table to new temp table
Drop production table
Rename new temp table to same as production table name
If there are any issues with moving the data the script should stop within the transaction and allow the database user to modify or rollback the changes. Toad doesn’t need to account for all the various scenarios that could cause this to fail, but you could create a structure for us that we can work within without asking us to parse tens to hundreds of pages of Generate Script looking for DROP TABLE and replacing it with our own approach.