ALTER TABLE … ALTER COLUMN

-- Change column type: VARCHAR(20) NULL --> VARCHAR(200) NOT NULL
ALTER TABLE Books ALTER COLUMN Title VARCHAR(200) NOT NULL
 
-- Allow nulls on the column Title (the type of the column has to be specified)
ALTER TABLE Books ALTER COLUMN Title VARCHAR(200) NULL

ALTER TABLE … ADD

-- Add a column if it does not exist
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' 
    AND TABLE_NAME='Books' AND COLUMN_NAME='IsActive') 
ALTER TABLE Books ADD IsActive BIT NOT NULL DEFAULT 1

ALTER TABLE … DROP

-- Drop a constraint
ALTER TABLE Books DROP CONSTRAINT DF__Books__IsActive__628FA481 -- CONSTRAINT keyword is optional
 
-- Drop a column (at first the DEFAULT contraint needs to be deleted)
ALTER TABLE Books DROP COLUMN IsActive -- a list of columns may be specified

Rename a column:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' 
    AND TABLE_NAME='Books' 
    AND COLUMN_NAME='NewFieldname') 
 
EXEC sp_RENAME 'Books.OldFieldname' , 'NewFieldname', 'COLUMN'
GO