Alter Table

Alter Table

Alter Table changes (alters) a table definition by altering, adding, or dropping columns and constraints, or by disabling or enabling constraints and triggers.

Example 1 of Alter Table

Adding a new column to an Emp table:

ALTER TABLE Emp ADD phone CHAR(16)
DEFAULT 'unlisted';

Example 2 of Alter Table

Dropping a column from an Emp table:

ALTER TABLE Emp DROP phone;

Example 3 of Alter Table

Changing the datatype of a column in a table:

ALTER TABLE Emp
ALTER COLUMN phone varchar;

Example 4 of Alter Table

Modifying a column:
[if earlier the column "name" is like varchar(20) and we want to change it to varchar(100) then do as follows:]

ALTER TABLE Emp ALTER COLUMN name varchar(100);

Example 5 of Alter Table

Adding a constraint

ALTER TABLE Emp ADD CONSTRAINT con_one UNIQUE (name);

con_one is the name of the constraint.

Example 6 of Alter Table

Constraints can also be dropped

ALTER TABLE Emp DROP CONSTRAINT con_one;

Renaming a column or table is not possible with Alter statement in SQL Server. For that you have to use sp_rename.