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
SyntaxTo 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
SyntaxTo 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
No comments:
Post a Comment