Altering a SQL Table on Production

Altering a SQL Table to add a column on Production can be risky affair as it contains vital production data in it. The sql script given below gives an idea how to go for it. In the example below, the table is getting altered to add the new column [Column6].

USE <>
GO

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_
	(
	[Key] [int] IDENTITY(1,1) NOT NULL,
	[Column1] [int] NOT NULL,
	[Column2] [int] NOT NULL,
	[Column3] [int] NULL,
	[Column4] [int] NOT NULL,
	[Column5] [varchar](50) NULL,
	[Column6] [int] NULL	
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TableName SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_TableName ON
GO
IF EXISTS(SELECT * FROM dbo.TableName)
	 EXEC('INSERT INTO dbo.Tmp_TableName ([Key],[Column1], [Column2],[Column3], [Column4], [Column5])
		SELECT [Key],[Column1], [Column2],[Column3], [Column4], [Column5] FROM dbo.TableName WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_TableName OFF
GO

DROP TABLE dbo.TableName
GO
EXECUTE sp_rename N'dbo.Tmp_TableName', N'TableName', 'OBJECT' 
GO
ALTER TABLE dbo.TableName ADD CONSTRAINT
	PK_TableName PRIMARY KEY CLUSTERED 
	(
	[Key]
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TableName]  WITH CHECK ADD  CONSTRAINT [FK_TableName_FKTable1] FOREIGN KEY([Column1])
REFERENCES [dbo].[FKTable1] ([Key])
GO

ALTER TABLE [dbo].[TableName]  WITH CHECK ADD  CONSTRAINT [FK_TableName_FKTable2] FOREIGN KEY([Column2])
REFERENCES [dbo].[FKTable2] ([Key])
GO

ALTER TABLE [dbo].[TableName]  WITH CHECK ADD  CONSTRAINT [FK_TableName_FKTable3] FOREIGN KEY([Column3])
REFERENCES [dbo].[FKTable3] ([Key])
GO

ALTER TABLE [dbo].[TableName]  WITH CHECK ADD  CONSTRAINT [FK_TableName_FKTable14] FOREIGN KEY([Column4])
REFERENCES [dbo].[FKTable4] ([Key])
GO

ALTER TABLE [dbo].[TableName]  WITH CHECK ADD  CONSTRAINT [FK_TableName_Table5] FOREIGN KEY([Column5])
REFERENCES [dbo].[FKTable5] ([Key])
GO


COMMIT