Forcing Management Studio to use ALTER TABLE instead of DROP/CREATE

Question

I'm wondering if is there a way to force MSSQL Management Studio to produce a script like this:

ALTER TABLE Mytable
ADD MyCol bit NOT NULL
CONSTRAINT MyColDefault
DEFAULT 0 WITH VALUES


ALTER TABLE [dbo].Mytable
ALTER COLUMN MyCol2 int NULL

GO 

when I alter a very simple property of a column on a table. If I do this in the designer and ask for the produced script, the script doesn't do such simple tasks, but instead copies all the data in a tmp table, drops the original table, renames the tmp table with the original table name. And, of course, drops and recreates every constraint and relationships.

Is there any option I can change to change this behaviour? Or, this may be possible, is there some danger I don't see in using the simple ALTER TABLE above?

thanks.

Solution 1

You can't change the behaviour, it's just that the default script it creates is not always the most efficient. It creates scripts in a format it knows will work, although frequently the results will be slow and resource-heavy. I recommend you get used to creating the script for all changes yourself as you can better optimize it. There's nothing wrong with what you've created (providing you have no existing constraints/dependencies on MyCol2 that would be invalidated by it becoming a nullable int)

Solution 2

Yes you can!

In SQL Server Management Studio, go into the table design mode for the table in question, and make your changes. However: do not click on the "Save" button, but instead right-click in the table design view, there should be a "Generate Change Script" item at the end of your context menu.

Click on that menu item and you'll get presented a pop-up dialog box which contains the T-SQL script needed to do those changes that you made to the table in the designer. Copy or save that T-SQL code, and cancel out of the designer, and voila - you have your change script!

UPDATE: Marco, sorry, I don't think there's any option to change the default behavior, at least not right now. You might want to file an enhancement request with Microsoft on Microsoft Connect to propose that - good idea, I would think!

Solution 3

Just to augment @marc_s's answer. In case you still can't generate the change script 'cause your table needs to be recreated, you need to go to Tools -> Options and under Designers -> Table and Database Designers unmark the option Prevent saving changes that require table re-creation.

Comments

[marco]

hi Marc, I'm sorry maybe I didn't make myself clear. I know this, and this is the way I've known that SQLMS does things in that way. I would have liked to know if there's any way to modufy its default behaviour. What you suggest is useful to use the script later, and indeed that's what I want to do. Too bad the script generated here is cumbersome and not resource efficient in usual situation, in my humble opinion. bye!

[Keith Walton]

This will give you an error instead of saving your changes - it won't make the designer user ALTER TABLE.