Page History: Schema Change Scripts - SQL Server

Compare Page Revisions

37 36 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 0 Current 37 36 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Fri, Dec 21, 2012, 8:46 AM

Sequence of Statements¶

• Drop foreign keys being created on existing tables
• Drop tables being created
• Drop code objects being created
• Drop columns being created
• Drop/recreate schema
• Create/alter tables ("alter" only includes adding columns)
• Create primary keys
• Create foreign keys
• Populate lookup tables
• Drop/recreate each code object in dependency order
  

Statement Syntax¶

Drop Column¶

if COL_LENGTH('TableName','ColumnName') is not null
    alter table dbo.TableName drop column ColumnName

Add Columns¶

alter table dbo.TableName ADD
     ColumnName  int        not null identity(1,1)
    ,ColumnName2 date       not null
    ,ColumnName3 varchar(3) null     constraint DF_TableName_ColumnName3 DEFAULT 'XYZ' WITH VALUES

Examples¶

Add a column

Note that there is NO COLUMN after ADD!

ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL

Add a non-nullable column with default values

ALTER TABLE MyTable ADD AddDate smalldatetime NOT NULL CONSTRAINT AddDateDflt DEFAULT getdate()

Add a nullable column with default values

ALTER TABLE MyTable ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT getdate() WITH VALUES

Drop a column

ALTER TABLE doc_exb DROP COLUMN column_b

Change a column

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL