Sql-server – Warn about table recreation on table change

sql serversql-server-2012ssms

By default, if you try to change a table using the designer making any of the following changes, you get an error message:

  • You change the Allow Nulls setting for a column.
  • You reorder columns in the table.
  • You change the column data type.
  • You add a new column.

Error message:

enter image description here

I know that one can turn this off by changing the "Prevent saving changes that require table re-creation' option in SSMS options.

I find that for some tables, (the ones with important data in them), I want this warning. However, for a lot of other tables, I don't care whether they get blown away and then re-created. Right now, I'm just constantly changing the setting back and forth, but what I'd really like is a way to be warned but still be given the option to proceed anyway.

Is there any setting anywhere that would cause behaviour like this?

Best Answer

No, the option is to either always prevent it or to not warn you at all.

What you should do, is merely learn and understand what types of changes will force SSMS to drop the table and re-create it, or stop using the designer UI altogether and use DDL. In some cases you can write DDL such that it doesn't have to drop and re-create the table (SSMS isn't smart enough for all of these cases), but in all cases at least you'll know what's going to happen.

Related Question