Alter Table in SQL

 ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype 

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name 

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

If we use CREATE TABLE and the table already exists in the table we will get an error message, so if we combine CREATE TABLE and ALTER TABLE we can create robust database scripts that gives no errors, as the example shown below:

if not exists (select * from dbo.sysobjects where id = object_id(N'[CUSTOMER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE CUSTOMER

(

CustomerId int PRIMARY KEY,

CustomerNumber int NOT NULL UNIQUE,

LastName varchar(50) NOT NULL,

FirstName varchar(50) NOT NULL,

AreaCode int NULL,

Address varchar(50) NULL,

Phone varchar(50) NULL,

 )

GO 

if exists(select * from dbo.syscolumns where id = object_id(N'[CUSTOMER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and name = 'CustomerId') 

ALTER TABLE CUSTOMER ALTER COLUMN CustomerId int 

Else

ALTER TABLE CUSTOMER ADD CustomerId int 

GO

if exists(select * from dbo.syscolumns where id = object_id(N'[CUSTOMER]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 and name = 'CustomerNumber') 

ALTER TABLE CUSTOMER ALTER COLUMN CustomerNumber int 

Else 

ALTER TABLE CUSTOMER ADD CustomerNumber int 

GO

...