Useful SQL migration patterns

databasemigrations.sql

--ADDING A COLUMN
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'NewColumnName' AND Object_ID = Object_ID(N'ExistingTableName'))
BEGIN
	ALTER TABLE ExistingTableName
	ADD [NewColumnName] BIT
END

--DROPPING A COLUMN
IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'NewColumnName' AND Object_ID = Object_ID(N'ExistingTableName'))
BEGIN
	ALTER TABLE ExistingTableName
	DROP COLUMN [NewColumnName]
END

--ADDING A COLUMN WITH CONSTRAINT
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'NewColumnName' AND Object_ID = Object_ID(N'ExistingTableName'))
BEGIN
	ALTER TABLE ExistingTableName
	ADD NewColumnName INT NOT NULL CONSTRAINT DF_ExistingTableName_NewColumnName DEFAULT 0
END

--DROPPING A COLUMN WITH CONSTRAINT
IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'NewColumnName' AND Object_ID = Object_ID(N'ExistingTableName'))
BEGIN
	ALTER TABLE dbo.ExistingTableName DROP CONSTRAINT DF_ExistingTableName_NewColumnName
	ALTER TABLE ExistingTableName
	DROP COLUMN NewColumnName
END