TSQL Tutorial : ALTER TABLE statement

The TSQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.

Add column in table

Syntax

To add a column in a table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name

  ADD column_name column-definition;

Example

Let's look at a TSQL ALTER TABLE example that adds a column in our earlier table named as Customer

Example:

ALTER TABLE Customer

  ADD Customer_PhNo varchar(50);

This TSQL ALTER TABLE example will add a column called Customer_PhNo to the customer  table.

Add multiple columns in table

Syntax
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:

ALTER TABLE table_name

  ADD (column_1 column-definition,

       column_2 column-definition,

       ...

       column_n column_definition);

Example

Let's look at  TSQL ALTER TABLE example that adds more than one column.
For example:

ALTER TABLE supplier

  ADD (customer_city varchar(50),

       Region varchar(45));

This TSQL ALTER TABLE example will add two columns, Customer_city as a varchar(50) field and region as a varchar(45) field to the supplier table.

Modify column in table

Syntax

To modify a column in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name

  MODIFY column_name column_type;

Example

Let's look at TSQL ALTER TABLE example that modifies a column.
For example:

ALTER TABLE Customer

  MODIFY Customer_name varchar(100) not null;

This TSQL ALTER TABLE example will modify the column called Customer_name to be a data type of varchar(100) and not null constraints.

Modify multiple columns in table

Syntax
To modify multiple columns in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name

  MODIFY (column_1 column_type,

          column_2 column_type,

          ...

          column_n column_type);

Example

Let's look at TSQL ALTER TABLE example that modifies more than one column.
For example:

ALTER TABLE Customer

  MODIFY (customer_name varchar(100) not null,

          city varchar(75));

This TSQL ALTER TABLE example will modify both the customer_name and city columns.

Drop column in table

Syntax

To drop a column in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name

  DROP COLUMN column_name;

Example

Let's look at TSQL ALTER TABLE example that drops (ie: deletes) a column from a table.
For example:

ALTER TABLE Customer

  DROP COLUMN customer_name;

This TSQL ALTER TABLE example will drop the column called customer_name from the table called supplier.

Rename column in table

Syntax

To rename a column in an existing table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name

  RENAME COLUMN old_name to new_name;

Example

Let's look at TSQL ALTER TABLE example that renames a column in a table.
For example:

ALTER TABLE customer

  RENAME COLUMN customer_name to custname;

This TSQL ALTER TABLE example will rename the column called customer_name to custname.

Rename table

Syntax

To rename a table, the TSQL ALTER TABLE syntax is:

ALTER TABLE table_name

  RENAME TO new_table_name;

Example

Let's look at TSQL ALTER TABLE example that renames a table.
For example:

ALTER TABLE customer

  RENAME TO client;

This TSQL ALTER TABLE example will rename the customer table to client