Click here to Skip to main content
14,767,352 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
Comments
CHill60 21-Feb-15 6:31am
   
Not clear. Do you mean how to get Management Studio to generate the script?

1 solution

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.

/* 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.
   

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