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, Feb 01, 2013, 9:41 AM


Sequence of Statements

1. Drop foreign keys being created on existing tables

2. Drop tables being created

3. Drop code objects being created

4. Drop default constraints on columns being created

5. Drop columns being created

6. Drop/recreate schema

7. Create/alter tables ("alter" only includes adding columns)

8. Create primary keys

9. Create foreign keys

10. Populate lookup tables

11. Drop/recreate each code object in dependency order

Statement Syntax

1. Drop foreign keys being created on existing tables

if OBJECT_ID('FK_OnTable_AgainstTable') is not null alter table OnTable drop constraint [FK_OnTable_AgainstTable]

2. Drop tables being created

if object_id('SchemaName.TableName') is not null drop table SchemaName.TableName

3. Drop code objects being created


4. 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

5. Drop Column

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

6. Drop/recreate schema

if exists (select 2 from sys.schemas where name = 'SchemaName') drop schema SchemaName
go    
create schema SchemaName authorization dbo
go

7. Create Tables and Add Columns

create table dbo.TableName (
     ColumnName  int         not null identity(1,1)
    ,ColumnName2 varchar(30) not null
    ,CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED 
    ( [ColumnName] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

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

8. Create Primary Keys

This is necessary only if the CREATE TABLE script(s) didn't create them.

ALTER TABLE TableName ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED (TableNameID ASC) 
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

9. Create foreign keys

ALTER TABLE OnSchema.OnTable WITH CHECK ADD CONSTRAINT [FK_OnTable_AgainstTable] 
FOREIGN KEY (OnColumn) REFERENCES AgainstSchema.AgainstTable (AgainstColumn)

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.