ALTER table script to Create new column within existing columns..

One of my client asked me to send me an alter table script that add a new column in table. I sent him alter table statement as follows

ALTER TABLE CacheTree ADD IsXML bit default(0);

He said I know this but my problem is to add news column on a specific position like 3rd column in table necessary for maintaining Standards but he did not have SSMS access? He added there is a way when you make a change in SSMS you can generate ALTER TABLE script through SSMS without saving that column and then you can use that statement to create your own column and table. He needs that script from me.

I have idea in mind that we might achieve this by using 

• Drop keys from the existing table
• Create new table with required column and insert data into newly created temp table.
• drop the existing table
• Rename the new table and create keys.

But i don't know how SSMS generate script for this. Now I have found the way and wants to share it with all who wants to know.

Enable Alter Changes through SSMS

You can enable check box using SSMS that enables option to generate Alter table script. Through 2008 R2 SSMS I have done following changes.

Here is the check box "Warn about table effected".

After that try to add a new column in CacheTree table on 3rd place.

From the screen shot you can see I have added "IsXML" column and then before saving that change I have gone to 

"Form Designer -- > Generate Change Script"

and I got following script from SSMS.

/* 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
CREATE TABLE dbo.Tmp_CacheTree
(
SearchId int NOT NULL,
UserCriteria xml NULL,
IsXML bit NULL,
CampaignSearch xml NULL,
CampaignType int NULL
)  ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_CacheTree SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.CacheTree)
EXEC('INSERT INTO dbo.Tmp_CacheTree (SearchId, UserCriteria, CampaignSearch, CampaignType)
SELECT SearchId, UserCriteria, CampaignSearch, CampaignType FROM dbo.CacheTree WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.CacheTree
GO
EXECUTE sp_rename N'dbo.Tmp_CacheTree', N'CacheTree', 'OBJECT' 
GO
ALTER TABLE dbo.CacheTree ADD CONSTRAINT
PK_SearchUserCampaigns PRIMARY KEY CLUSTERED 
(
SearchId
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


GO
COMMIT

Now look at the script It did same what I was thinking but I got regenerated script from SSMS.