Over the lifespan of a database driven application you may find that your existing database schema is not sufficient for new functionality. To maintain the integrity of your existing data Sybase and other SQL Servers enable administrators to modify existing tables by adding columns and constraints.

SQL Format

The basic format for the add column script is as follows

ALTER TABLE table_name
     ADD column_name datatype default null | not null

How the script works is that you specify the name of the table you want to modify then use the add clause to define the column you want to add to the table. In the add clause you specify the column name, type, null or not null, and if not null the default value to insert into existing data rows in the table.

Example

Lets assume we are starting with the following database table USERS

Assuming the above table and data lets add a column called ACTIVE which will tell us if that user is active or inactive.

ALTER TABLE USERS
    ADD ACTIVE tinyint 1 not null

After running this script we will have the following table with the ACTIVE column for all existing records having a value of 1

Resources

• Sybase – Transact-SQL Add Column Manual