Alter table script

Question

Alter table script

Alberto Vásquez

SSC Veteran

Points: 282
More actions
August 3, 2012 at 10:32 am

#264772

In Microsoft SQL Server 2008 R2 Management Studio it's possible to generate scripts for a particular SQL function, such as SELECT, INSERT, UPDATE, etc. However, the ALTER To option is disabled in the Script Table as menu option!
Some times I altered a table (i.e. new columns or datatype changes) in my development environment and I need to copy those modifications to my production server.
Have any of you faced this problem?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

How do you make those changes in development? If you use an ALTER TABLE statement that you write, you would need to use that same script in Test, QA, Production.

Alberto Vásquez SSC Veteran Points: 282 More actions · Answer 2

Thanks for your answer. I always use the DESIGN mode to make modifications. In DESIGN mode there is a way to create an alter script for a table (Open the table to modify, make changes, select the column that changed --> right click --> Generate Change Script.) buy I would prefer a script for the whole schema. Is there any way?

Saludos!!

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

Alberto Vásquez (8/3/2012)
Thanks for your answer. I always use the DESIGN mode to make modifications. In DESIGN mode there is a way to create an alter script for a table (Open the table to modify, make changes, select the column that changed --> right click --> Generate Change Script.) buy I would prefer a script for the whole schema. Is there any way?
Saludos!!

There is no way that a given database is going to be able to generate a change script to get a different database the same. You should consider changing the way you do your work. Ideally you should be writing your own alter table scripts. If you are unwilling/unable to do that then you need to save the change scripts from the design window.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323755 More actions · Answer 4

To add What Sean is saying; changes are often not just DDL schema changes; when you add a new table, you may have added new rows in that new table as well (think lookup tables, all zip codes, etc.

those need to be part of a change script for deployment as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Once you make the change, SQL Server has no way to know what changed. Capturing the change script using the designer is one way. The only other way I would suggest is getting a tool like RedGate's SQL Compare and use it to generate changes scripts to push changes from development to test/qa/prod.

Alberto Vásquez SSC Veteran Points: 282 More actions · Answer 6

Thank's to all of you. I'll take all your suggestions,

Saludos!!