MS SQL cheat sheets for common tweaks

add-if-not-exists.sql

-- Add column to table
if not exists (
  select * 
  from   sys.columns 
  where  object_id = OBJECT_ID('MyTable') 
  and 	 name = 'MyNewColumn'
)
begin
	alter table MyTable
	add MyNewColumn nvarchar(20) null
end
go

-- ----------------------------------------
-- Add table
if (not exists (select * 
                 from information_schema.tables 
                 where table_schema = 'MySchema' 
                 and  table_name = 'MyTable'))
begin
    -- create table MyTable
end

alter-columns.sql

-- Make a NULLable column NOT NULLable
-- Example here is a bit field (true/false) and we set a default val of false (0)
-- It's your choice to include/exclude the 'default x' statement

alter table MyTable
alter column MyColumn bit not null default 0
go

-- ------
-- Assign a primary key
-- N.b. you CANNOT combine multiple alterations under one alter table statement! :(

alter table MyTable
add constraint PK_MyColumn primary key clustered (MyColumn)
go

-- ------
-- Assign primary and foreign keys
-- During table creation:

create table VideoStockist
(
	ID 				bigint identity(1,1) primary key,
	StoreID			bigint not null,
	VideoID			bigint not null,
	constraint FK_StoreID
		foreign key (StoreID)
		references Store (ID)
		on delete cascade
		on update cascade,
	constraint FK_VideoID
		foreign key (VideoID)
		references Video (ID)
		on delete cascade
		on update cascade
)

-- ------
-- Assign Foreign Keys
-- After table creation:

alter table VideoStockist 
add constraint FK_VideoID
	foreign key (VideoID)
	references Video (ID)
	on delete cascade
	on update cascade
go