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