SELECT [softwareThoughts] from Tyler

When working on a database with a team of people all making changes, it's a great idea to make all change scripts idempotent, which in this case means "always having exactly the same output as the first time it was run." See Phil's article for a great demonstration of the principle and practice.

I have found that using the object_id() function and the system tables allows me to write a little less code than the information_schema view require - I'm too lazy to type information_schema.referential_constraints too many times. All tables, indexes, check constraints, default values, and foreign key constraints can be tested for existence using object_id('objectName'), which returns null if the object isn't there in the database.
The syscolumns table can be used to test for column existence.

Note that in SQL Server 2005, the tables have been renamed to sys.objects and sys.columns. I haven't tested this with those tables, but with my SQL Server 2005 still allows me to use the table names from SQL Server 2000.

Be careful with check constraints and foriegn key constraints that are autogenerated by the database system: the names given by one instance of SQL Server will be different from that given by another, so that if you're checking for existence of a constraint named 'FK__tblMyTab_O_9t5325', your script might only be idempotent on one machine. In fact, it may throw errors on any other instance of the database.

Two ways to deal with this:
1) When creating database objects, always name your constraints. This has a slightly higher overhead but can make it much easier to change the thing using DDL later on. Here's an example of how to achieve that:

IF object_id('dbo.Apples') is null
 CREATE TABLE [dbo].[Apples](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Description] [nvarchar](50) ,
 CONSTRAINT [PK_Apples_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ,
 CONSTRAINT [UKey_Apples] UNIQUE NONCLUSTERED ([Description] ASC)
 )
GO

IF object_id('dbo.Oranges') is null
BEGIN
 CREATE TABLE [dbo].[Oranges](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Description] [nvarchar](50) ,
 CONSTRAINT [PK_Oranges_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ,
 CONSTRAINT [UKey_Oranges] UNIQUE NONCLUSTERED ([Description] ASC)
 )
END
GO

IF NOT EXISTS (SELECT 1 from syscolumns
  where id = object_id('Apples')
  and name='OrangeId')
BEGIN
 ALTER TABLE Apples
  Add OrangeId int not null
  constraint DF_Apples_OrangeId default 1 WITH VALUES
END

IF object_id('FK_Apples_Oranges') IS NULL
BEGIN
 ALTER TABLE Apples
 ADD CONSTRAINT FK_Apples_Oranges
 FOREIGN KEY (OrangeId)
 REFERENCES Oranges (Id)
END

IF NOT EXISTS (SELECT 1 FROM syscolumns
  WHERE id = object_id('Apples')
  and name = 'PickedOn')
BEGIN
 ALTER TABLE Apples ADD PickedOn DateTime
 CONSTRAINT DF_Apples_PickedOn DEFAULT GetDate()
END

--Note that if I try to drop the PickedOn column at this point the command will fail
--because of the default value. Luckily, there's a name associated with that constraint:

IF object_id('DF_Apples_PickedOn') is not null
BEGIN
 ALTER TABLE Apples
 DROP DF_Apples_PickedOn
END

if object_id('UKey_Apples') is not null
BEGIN
 alter table Apples drop constraint UKey_Apples
END

if object_id('PK_Apples_Id') is not null
BEGIN
 alter table Apples drop constraint PK_Apples_Id
END

2) If you already have objects with weird names, you can still find them using the
information_schema views:

declare @constraint_name sysname,
 @cmd varchar(1000)
while exists(    select 1
     from information_schema.referential_constraints
    where constraint_name like 'FK__tblTask%')
begin
 SELECT @constraint_name = (
   select top 1 constraint_name
     from information_schema.referential_constraints
    where constraint_name like 'FK__tblTask%')
 PRINT 'Dropping constraint ' + @constraint_name
 SELECT @cmd = 'ALTER TABLE dbo.tblTask_Object
    DROP CONSTRAINT ' + @constraint_name
 EXEC (@cmd)
end