Allow Tables to be dropped and re-created

Using this option makes life easier

​

​

If you use designers to change your tables in SSMS, you’ll eventually run into this error: “Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.”

This will show up for all sorts of changes that don’t seem like a big deal to us, but to the database requires an extra step.

Fortunately, you can tell SSMS to solve this automatically for you.

​

Go to Tools -> Options in SSMS

Un-Click “Prevent saving changes that require…”

Scroll down on the list on the left until you see “Designers”.

Click on Designers, then Unclick “Prevent saving changes that require table re-creation”

Then hit [OK]

You’re done!

Now SSMS will drop and re-create tables if needed.

(No need to worry, it will re-insert all the data into the new table).

If your doing this on tables with hundreds of thousands of rows, it may lock up the server while it re-inserts everything: Please make sure you test table changes in development before you do it on a production server!