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, Aug 29, 2014, 9:11 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 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. Create check constraints
11. Populate lookup tables
12. Drop/recreate indexes
13. Drop/recreate each code object in dependency order
    

Template ¶

/*==================================================================================================
    SCRIPT      
                                                                                                    
    AUTHOR                                                                          
                                                                                                    
    DATE        
                                                                                                    
    SYSTEM      
                                                                                                    
    PURPOSE     This script 
                                                                                                    
==================================================================================================*/

/*-- Drop Foreign Keys Begin Created -------------------------------------------------------------*/
/* Example
if OBJECT_ID('dbo.FK_OnTable_AgainstTable') is not null 
	alter table dbo.OnTable drop constraint FK_OnTable_AgainstTable
*/

/*-- Drop Tables Being Created -------------------------------------------------------------------*/
/* Example
if object_id('dbo.TableName') is not null drop table dbo.TableName
*/

/*-- Drop Column Constraints Being Created -------------------------------------------------------*/
/* Example
if object_id('DF_OnTable_OnColumn') is not null
    alter table dbo.OnTable drop constraint DF_OnTable_OnColumn
*/

/*-- Drop Columns Being Created ------------------------------------------------------------------*/
/* Example
if COL_LENGTH('TableName', 'ColumnName') is not null
    alter table dbo.TableName drop column ColumnName
*/

/*-- Drop/Recreate Schemas -----------------------------------------------------------------------*/
/* Example
if exists (select 2 from sys.schemas where name = 'SchemaName') drop schema SchemaName
go    
create schema SchemaName authorization dbo
go
*/

/*-- Create Tables and Add Columns ---------------------------------------------------------------*/
/* Examples
create table dbo.TableName (
     TableNameID                    int             not null identity(1,1)
    ,ColumnName2                    varchar(30)     not null
    ,CreatedBy                      nvarchar(128)   not null constraint DF_TableName_CreatedBy  DEFAULT ('system')
    ,CreatedOn                      datetime        not null constraint DF_TableName_CreatedOn  DEFAULT (getutcdate())
    ,UpdatedBy                      nvarchar(128)   not null constraint DF_TableName_UpdatedBy  DEFAULT ('system')
    ,UpdatedOn                      datetime        not null constraint DF_TableName_UpdatedOn  DEFAULT (getutcdate())
    ,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
*/

/*-- Create Foreign Keys -------------------------------------------------------------------------*/
/* Example
ALTER TABLE dbo.OnTable WITH CHECK ADD CONSTRAINT FK_OnTable_AgainstTable
FOREIGN KEY (OnColumn) REFERENCES dbo.AgainstTable (AgainstColumn)
*/

/*-- Create Column Constraints -------------------------------------------------------------------*/


/*-- Populate Lookup Tables ----------------------------------------------------------------------*/


/*-- Populate Foreign Key Tables -----------------------------------------------------------------*/


/*-- Make Populated Columns Non-Nullable ---------------------------------------------------------*/
/*
alter table dbo.Table alter column ColumnName DataType not null
*/

/*-- Drop/Recreate Indexes -----------------------------------------------------------------------*/
/* Example
IF EXISTS(SELECT 2
            FROM sysindexes si
            INNER JOIN sysobjects so
                   ON so.id = si.id
           WHERE si.[Name] = N'IX_TableName_ColumnName'
             AND so.[Name] = N'TableName')
BEGIN
    DROP INDEX IX_TableName_ColumnName ON dbo.TableName
END
GO
CREATE INDEX IX_TableName_ColumnName ON dbo.TableName
(
    ColumnName ASC
) 
GO
*/

Statement Syntax¶

1. Drop foreign keys being created on existing tables¶

if OBJECT_ID('SchemaName.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 Constraint on Columns Being Created¶

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

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

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)

10. Create Check Constraints¶

BooleanExpression can contain the names of columns in the table. If BooleanExpression is rendered false by a DML statement, then the DML statement is prohibited.

ALTER TABLE SchemaName.TableName WITH CHECK ADD CONSTRAINT CK_SchemaName_TableName_ConstraintName CHECK  
BooleanExpression
GO

ALTER TABLE SchemaName.TableName CHECK CONSTRAINT CK_SchemaName_TableName_ConstraintName
GO

11. Populate Lookup Tables¶

12. Drop/recreate Indexes ¶

IF EXISTS(SELECT 2
            FROM sysindexes si
            INNER JOIN sysobjects so
                   ON so.id = si.id
           WHERE si.[Name] = N'IX_TableName_ColumnName'
             AND so.[Name] = N'TableName')
BEGIN
    DROP INDEX [IX_TableName_ColumnName] ON [dbo].[TableName]
END
GO
CREATE INDEX [IX_TableName_ColumnName] ON [dbo].[TableName]
(
    [ColumnName] ASC
) 
GO

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

Add a check constraint

ALTER TABLE dbo.MyTable WITH CHECK ADD CONSTRAINT CK_MyTable_Xor CHECK  
( ( ( ( (case when [OptionId] IS NOT NULL then (1) else (0) end
+case when [NumericValue] IS NOT NULL then (1) else (0) end)
+case when [DateValue] IS NOT NULL then (1) else (0) end)
+case when [PrecisionValue] IS NOT NULL then (1) else (0) end)=(1)))
GO

ALTER TABLE dbo.MyTable CHECK CONSTRAINT CK_MyTable_Xor
GO