SQL SERVER Alter table statement with Examples

ALTER TABLE STATEMENT

DESCRIPTION:

This SQL blog explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table .

RECOMMENDED : SQL SERVER Select Statement with Examples

ADD COLUMN IN TABLE

Syntax

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

ALTER TABLE table_name
  ADD column_name column-definition;

Example

Let's look at a SQL ALTER TABLE example that adds a column.

For example:

ALTER TABLE Employees
  ADD Employee_Name varchar(50);

This SQL ALTER TABLE example will add a column called Employee_Name to the Employees 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 SQL ALTER TABLE example that adds more than one column.

For example:

ALTER TABLE Employees
  ADD (Employee_Name varchar(50),
       Salary decimal(15,2));

This SQL ALTER TABLE example will add two columns, Employee_Name as a varchar(50) field and Salary as a decimal(15,2) field to the Employees table.

MODIFY COLUMN IN TABLE

Syntax

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

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

Example

Let's look at an example of how to modify a column called Employee_Name using the ALTER TABLE statement.

ALTER TABLE Employees
  ALTER COLUMN Employee_Name VARCHAR(100) NOT NULL;

MODIFY MULTIPLE COLUMNS IN TABLE

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition,
  ALTER COLUMN column_name TYPE column_definition,
  ...
;

Example

Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called Employee_Name and Salary.

ALTER TABLE Employees
  ALTER COLUMN Employee_Name TYPE NVARCHAR(100),
  ALTER COLUMN Employee_Name SET NOT NULL,
  ALTER COLUMN Salary TYPE VARCHAR(50);

DROP COLUMN IN TABLE

Syntax

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

ALTER TABLE table_name
  DROP COLUMN column_name;

Example

Let's look at an example that drops (delete) a column from a table.

For example:

ALTER TABLE Employees
  DROP COLUMN Employee_Name;

This SQL ALTER TABLE example will drop the column called Employee_Name from the table called Employees.

RENAME COLUMN IN TABLE

Syntax

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

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

Example

Let's look at an example that renames a column in the Employees table from Employee_Name to EName.

sp_rename 'Employees.Employee_Name', 'EName', 'COLUMN';

RENAME TABLE

Syntax

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

sp_rename 'table_name', 'new_table_name';

Example

Let's look at an example that renames a table called Employees to the new name tblEmployee.

sp_rename 'Employees', 'tblEmployee';

.NET CODE

Monday, 22 February 2016