Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Schema Change Scripts - SQL Server

Compare Page Revisions



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


Page Revision: Fri, Dec 21, 2012, 9:14 AM


Sequence of Statements

  • Drop foreign keys being created on existing tables
  • Drop tables being created
  • Drop code objects being created
  • Drop default constraints on columns 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 Default Constraint

if exists (select * from sys.objects where name = 'DF_SchemaName_TableName_ColumnName')
    alter table SchemaName.TableName drop constraint DF_SchemaName_TableName_ColumnName

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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.