DDL 'IF not Exists" conditions to make SQL scripts re-runnable

 

As a part of continuous integration we are using deployment of database scripts, which makes very important to make the scripts re-runnable. Some checks for DDL elements are not obvious, and I decided to put hem in one place.Most answers were found on StackOverflow

 

--Column does not exists
if NOT Exists(select * from sys.columns where Name = N'CreatedDate' and Object_ID = Object_ID(N'MyTableName'))

--Check if primary key does not exists
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'MyTableName' )
or
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME= 'PK_MyTableName')

--Check if foreign key does not exists
if NOT Exists(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='FK_MyRefTable_MyInstanceTable')
or (from here)
IF NOT EXISTS (SELECT name FROM sys.foreign_keys WHERE name = 'FK_Name')
--http://stackoverflow.com/questions/869081/a-way-to-check-if-foreign-key-exists-in-sql-2005

--Check if DEFAULT does not exist
if NOT Exists(select * from sysobjects where xtype='D' and NAME='DF_MyTableName_MyColumnName')
 

Check if an INDEX doesn't exist
--from http://stackoverflow.com/questions/2689766/how-do-you-check-if-a-certain-index-exists-in-a-table
IF NOT EXISTS(SELECT * FROM sys .indexes WHERE name = '_index_MyTableName_MyColumnName_MyColumn2' AND object_id = OBJECT_ID('MyTableName' ))
CREATE NONCLUSTERED INDEX [_index_MyTableName_MyColumnName_MyColumn2] ON [dbo].[MyTableName]
(
        MyColumnName ASC ,
       MyColumn2 DESC
)
go

Check if STATISTICS name doesn't exist
IF NOT EXISTS(SELECT name  FROM sys .stats WHERE name = '_dta_stat_MyTableName_MyColumn1_MyColumn2' AND object_id = OBJECT_ID('MyTableName' ))
CREATE STATISTICS _dta_stat_MyTableName_PDFAttachmentName_MailID_DateCreated ON [dbo].[MyTableName] (MyColumn1,MyColumn2)
go

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc') exec('CREATE PROCEDURE [dbo].[MyProc] AS select 1')
GO ALTER PROCEDURE [dbo].[MyProc] AS
--body of your SP

GO

 Check if VIEW doesn't exist
From Books Online: "CREATE VIEW must be the first statement in a query batch."
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MyView]' ))
exec(' create view MyView AS select 1')
GO
 
Alter VIEW [dbo]. [MyView]
AS
....
 
 
 
 
posted @ Friday, August 12, 2011 7:20 AM
Print

Comments on this entry:

No comments posted yet.

Your comment:



(not displayed)

 
 
 
 

Live Comment Preview:

 
«November»
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456