SQL Server: Unable to Change the Table After Creation

SQL Management Studio is awesome tool. It will provide punch of features to the developer like from creating tables to backup the DB. It provides that much of nice GUI.

Problem:

By default, if you create a table using the SSMS after save it, if you want to modify some column name or datatype means it won't allow you.

Whenever you changed the table's column or it's datatype etc SQL Server will recreate the table again. But by default this option is disabled. That is, it won't allow you to perform this operation.It will throw the below error:

"Saving changes is not permitter. The changes you have made require the following tables to be dropped and re-created. You have either made
changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."


error

Solution:

Go to Tools -> Option.

In the Option Dialog -> Expand "Designers" node -> Select "Table and Database Designers"

In the right hand side, uncheck the check box "Prevent saving changes that require table re-creation" and click "OK".

Table and database

That's all! Now the SQL Server allows you change the table.

Ebook Download
View all
Learn
View all