Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.