Hi All,
Ever tried to make a change to a SQL database table only to get a message telling you it is unable to carry out the change as it would have to drop and recreate the table?
This is one of these things that you encounter once, correct and then forget about. I encountered it today on a new database server I had set up so I thought I'd post the solution here.
Basically Microsoft decided in their wisdom to by default not allow users to carry out changes to tables that would involve the table being droppped then recreated. On one hand, fair enough, saftey first and all that. But on the other, annoying - I know what I'm doing!!!
So how to fix, in SQL Server Management Studio, select the Tools menu then Options... and open the Options dialog.
In the dialog, you should see a Designers option and under this a Table and Database Designers option.
In the Table and Database Designers option, there will be a checked checkbox labelled Prevent saving changes that require table recreation. Uncheck this option and hit the OK button and then forget about it until you create a new database server and curse Microsoft for making you have to go and search for this article again.... :)
