ALTER TABLE permissions default to the table owner, members of the sysadmin
fixed server role, and to members of the db_owner and db_ddladmin fixed database roles.
Examples
A. Alter a table to add a new column
This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column.
CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO
B. Alter a table to drop a column
This example modifies a table to remove a column.
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
C. Alter a table to add a column with a constraint
This example adds a new column with a UNIQUE constraint.
CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO
