"SQL Spackle" is a collection of short articles written based on multiple requests for similar code. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks".

--Phil McCracken

Introduction

Not everyone knows how to write their own T-SQL code. Quite often, DBAs and Developers default to using the DROP and CREATE generated by the SQL Server engine. Most of the time these scripts work as advertised. But with the changes made in SQL Server 2005 (and on) to the schema structure and behavior, there are times when the engine-generated code does not work properly and there are few articles to explain why.

With this article, I hope to explain the problem and two possible solutions that can be used in any environment. Please note, I am not using (or discussing) DBO owned tables.

The Setup

Our example table will be a Product table.

/****** This is a new table. Drop the table if it already exists. ******/IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Test].[Product]') AND type in (N'U'))
 DROP TABLE [Test].[Product]
GO
/****** This is a new schema. Drop the schema if it already exists. ******/IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Test')
 DROP SCHEMA [Test]
GO
CREATE SCHEMA Test AUTHORIZATION dbo;
GO
CREATE TABLE Test.Product (
   ProductID INT IDENTITY(1,1) NOT NULL,
   ProductName VARCHAR(30) NOT NULL,
   ProductType VARCHAR(50) NULL);
GO

Note: Since this article is all about the drop process, I'm not going to populate the table with data.

Scope Creep

After a few months go by, the Business Unit realizes they want to add a pricing column to the Product table. One of our business rules dictates that no money column can be NULL. If there is no monetary value inserted when the record is inserted, then we must populate the money as $0.00. So we use default constraints for this.

An additional "business rule" for the IT team is that all schema changes must be accompanied by both a DROP and a CREATE statement. This helps us track the most recent object change as our code moves through environments. First I write the ALTER TABLE statement to add the column and constraint and execute the code in our development environment.

ALTER TABLE Test.Product
 ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO

Because I can never remember how to write the actual DROP CONSTRAINT statement, I go to SQL Server, right click the constraint, and generate the DROP statements to a clipboard so I can paste it right into my script window.

This is what SQL Server gives me:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Product_Cost]') AND type = 'D')
 BEGIN 
  ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost] 
 END
GO

Now I write DROP COLUMN statement and rearrange my script so it reads as follows:

/****** New column and constraint. DROP Constraint and Column if they already exist. ******/IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Product_Cost]') AND type = 'D')
 BEGIN
  ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost]
 END
GO
ALTER TABLE Test.Product
 DROP COLUMN Cost;
GO
ALTER TABLE Test.Product
 ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO

The Big Test

I always run my scripts in Dev, multiple times, to make sure they work before I save and promote them through the environments. But today SQL Server has a surprise for me:

Msg 5074, Level 16, State 1, Line 2

The object 'DF_Product_Cost' is dependent on column 'Cost'.

Msg 4922, Level 16, State 9, Line 2

ALTER TABLE DROP COLUMN Cost failed because one or more objects access this column.

Wait. What? SQL Server generated the script, so the constraint should drop. I troubleshoot the DROP statement by running the SELECT inside the IF EXISTS clause.

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Product_Cost]') AND type = 'D'

As picture 1 shows, there's nothing in sysobjects with that constraint name, so the DROP worked, right? 

Picture 1 - "Move along. Nothing to see," says Sysobjects

To double-check, I go into Object Explorer and check the table properties.

Picture 2 - "Ha! Tricked you," claims Object Explorer.

There that constraint is. Still showing in Object Explorer as in Picture 2

Questions and Answers

Why won't SQL Server delete the constraint with the script it generated?

To figure this out, I tried searching sysobjects by name, then finally gave up and did a SELECT * on the table ordered by xtype (not type) and name. The Default Constraint showed up exactly as expected, which tells me something is wrong with the engine-generated script. So I looked up Object_ID() in Books Online and discovered something interesting.

"When the parameter to a system function is optional, the current database, host computer, server user, or database user is assumed."

Books Online isn't terribly clear on what Object_ID() is looking for, but I believe this above line means that the function assumes all objects are in DBO unless otherwise specified.

I tested this three ways. Two of these methods altered the engine-generated script. The third method I wrote from scratch.

Method 1

Remove the function and the square brackets, matching on name instead of id.

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'DF_Product_Cost' AND type = 'D')
 BEGIN
  ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost]
 END
GO

Method 2

Add the schema inside the Object_ID() function.

/****** New column and constraint. DROP Constraint and Column if they already exist. ******/IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Test].[DF_Product_Cost]') AND type = 'D')
 BEGIN
  ALTER TABLE [Test].[Product] DROP CONSTRAINT [DF_Product_Cost]
 END
GO

Method 3

My personal favorite since I can never remember how to word the actual sysobjects code and I'm a big fan of the Information_Schema catalog views.

IF (SELECT Column_Name FROM Information_Schema.Columns
       WHERE Table_Name = 'Product' AND Column_Name = 'Cost' AND Column_Default IS NOT NULL) IS NOT NULL
 BEGIN
  ALTER TABLE Test.Product DROP CONSTRAINT [DF_Product_Cost];
 END;
GO

Now I don't strictly need the Column_Default check in that DROP CONSTRAINT code, but it gives my code additional security. If Column_Default is NULL, that means the constraint doesn't exist so won't error out on a DROP statement that shouldn't be run.

All three methods worked without error or confusion. Here's what my final solution looks like:

IF (SELECT Column_Name FROM Information_Schema.Columns
       WHERE Table_Name = 'Product' AND Column_Name = 'Cost' AND Column_Default IS NOT NULL) IS NOT NULL
 BEGIN
  ALTER TABLE Test.Product DROP CONSTRAINT [DF_Product_Cost];
 END;
GO
ALTER TABLE Test.Product
 DROP COLUMN Cost;
GO
ALTER TABLE Test.Product
 ADD Cost MONEY NOT NULL CONSTRAINT DF_Product_Cost DEFAULT 0.00;
GO

Summing Up

You can choose whichever solution you like, but the thing to remember is to double-check any SQL generated DROP code related to non-DBO schema objects. More than likely Microsoft will fix this issue at this point, but until then, just remember to tweak and test rather than assuming the engine will give you the correct solution.

Crack filled!

I hope this helps resolve the confusion.

Brandie Tarvin