ALTER TABLE STATEMENT

 The “Alter table” statement is used to add, delete, or modify columns in an existing table.

Syntax:

Add a column:

ALTER TABLE <Table name>

ADD ColumnName datatype

Example:

ALTER TABLE Sample_Table ADD Active SMALLINT

Result:

 SELECT * FROM Sample_Table

Drop column:

ALTER TABLE <Table name>

DROP COLUMN ColumnName

Example:

ALTER TABLE Sample_Table DROP COLUMN Active

Result:

SELECT * FROM Sample_Table

Change data type of a column:

ALTER TABLE <Table name>

ALTER COLUMN ColumnName datatype

Example:

The “Sample_Table” Column “ID” is having “Integer (INT)” datatype. I want to change it as varchr(10).

SQL statement:

                        ALTER TABLE Sample_Table ALTER COLUMN ID VARCHAR(10)

NOTE:

Using “SP_HELP <Table name>” syntax we can find the column data types in a table.

Before change: SP_HELP Sample_Table

After change: SP_HELP Sample_Table