Is not unusual, having created a table, to find that you need to modify or alter it. You do this with the ALTER TABLE Transact-SQL statement. It follows this general syntax:
This is the name of the table that you wish to modify.
This specifies whether the data in the table is checked against foreign key or check constraints.
This indicates that you wish to modify the column named in column_name. You can specify a new datatype, allow nulls and ROWGUIDCOL flags, and set precision and scale.
This indicates that you wish to add a column named in column_definition. You can specify any of the column-definition arguments in the CREATE TABLE statement we looked at earlier in this module.
This indicates that you wish to delete the constraint named with constraint_name.
With the DROP statement, this indicates that you wish to delete the column named with column_name.
This enables or disables a constraint.
This enables (with the CHECK option) or disables (with NOCHECK) all constraints.
This enables or disables a trigger.
This enables (with the ENABLE option) or disables (with DISABLE) all triggers.
ALTER TABLE statement syntax [SQL-Server]
The ALTER TABLE statement syntax
This is the caption for Layout Figure Tag
The ALTER TABLE statement uses the following syntax: table is the name of the table that you wish to modify. WITH CHECK | WITH NOCHECK specifies whether the data in the table is checked against foreign key or check constraints. ALTER COLUMN column_name indicates that you wish to modify the column named in column_name. You can specify a new datatype, allow nulls and ROWGUIDCOL flags, and set precision and scale. ADD column_definition indicates that you wish to add a column named in column_definition.
You can specify any of the
column-definition arguments in the CREATE TABLE statement we looked at earlier in this module. DROP CONSTRAINT constraint_name indicates that you wish to delete the constraint named with constraint_name. DROP COLUMN column_name indicates that you wish to delete the column named with column_name. CHECK | NOCHECK CONSTRAINT enables or disables a constraint. ALL enables (with the CHECK option) or disables (with NOCHECK) all constraints. ENABLE | DISABLE TRIGGER enables or disables a trigger. ALL enables (with the ENABLE option) or disables (with DISABLE) all triggers.
For example, here's how you would modify the employee table to add a column salary with a datatype of smallmoney:
ALTER TABLE employee ADD salary smallmoney
This statement will alter the table created earlier to drop the cost column:
ALTER TABLE Inventory
DROP COLUMN Cost
In the next lesson, we will cover the simple task of deleting a table.
