Click here to Skip to main content
15,351,370 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to get alter table script in sql server 2008?
Posted
Updated 15-Jul-21 14:15pm
Comments
CHill60 21-Feb-15 6:31am
   
Not clear. Do you mean how to get Management Studio to generate the script?

It's not that simple, because the option is greyed out (understandably, since there are no changes in a saved table)
But, you can do it.

Open SSMS, and select your database in the Object explorer pane.
Open the Tables branch, and right click the table you want to script.
From the menu, select "Design"
Make a trivial change (switch a "Allow Nulls" column to "Yes" and back to "No") or make genuine changes that you need to make.
DO NOT SAVE!
Now right click in the upper pane (not the pane with the "Column Properties" tab).
Select "Generate Change Script..." from the context menu.
You will be asked if you want to save this to a text file.

SQL
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.MyTable SET (LOCK_ESCALATION = TABLE)
GO
COMMIT


Note that this doesn't contain full info on the table structure.
If you want full info in the ALTER script, generate a CREATE TO script instead, and change the instruction to ALTER manually.
   
Hello
I already did the steps, but the disabled option Generate Change Script still appears.
Please validate me if there is anything else to do after I activate the check of Generate Change Script option.
   
Comments
Richard Deeming 16-Jul-21 4:00am
   
Your question is not a "solution" to someone else's question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900