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 exists
if NOT Exists(select * from sysobjects where xtype='D' and NAME='DF_MyTableName_MyColumnName')
posted @ Friday, August 12, 2011 7:20 AM