alter table (core)

Syntax

Transact-SQL Syntax

alter table [database.[owner].]table_name

{add column_name datatype
[default {constant_expression | user | null}]
 {[{identity | null}]
 | [[constraint constraint_name]
 {{unique | primary key}
 [clustered | nonclustered]
 [with {fillfactor | max_rows_per_page} = x]
 [on segment_name]
 [references [[database.]owner.]ref_table
[(ref_column)]
 | check (search_condition)}]}...
 {[, next_column]}...

| add {[constraint constraint_name]
 {unique | primary key}
 [clustered | nonclustered]
 (column_name [{, column_name}...])
 [with {fillfactor | max_rows_per_page} = x]
 [on segment_name]
 | foreign key (column_name [{, column_name}...])
 references [[database.]owner.]ref_table
[(ref_column [{, ref_column}...])]
 | check (search_condition)}

| drop constraint constraint_name

| replace column_name
default {constant_expression | user | null}

| partition number_of_partitions

| unpartition}

ODBC Syntax

ALTER TABLE base_table_name

{ADD column_identifier datatype

|ADD(column_identifier datatype[,column_identifier datatype]...)

|DROP[COLUMN]column_identifier[CASCADE|RESTRICT]}

Parameters

• null – specifies that you should assign a NULL value when a value is not provided during an insertion.
• table_name – is the name of the table to be changed.
• column_name – is the name of a column to be added.
• datatype – is any of the system datatypes except Bit. If the transformation mode is passthrough, the datatype is expressed as an ODBC datatype.
• next_column – indicates that you can include additional column definitions separated by commas, using the same syntax described for a column definition.

Examples

• Example 1 – adds the manager_name column to the publishers table. For each existing table row, a NULL value is assigned to the new column.

  alter table publishers

  add manager_name varchar(40) null

Usage