alter table (core)

Description

This command provides these functions:

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

alter table publishers
add manager_name varchar(40) null

This adds the manager_name column to the publishers table. For each existing table row, a NULL value is assigned to the new column.

Usage