SQL Error : Column already has a DEFAULT bound to it

The Problem

Today I was testing an application that using SQL server 2008 DB. I got an error in my application says that a table is missing in the DB. I decided to generate CREATE SQL script from other DB that has the missing table and execute in this target DB.

While executing the CREATE SQL script I got these errors:

There is already an object named 'SANotify' in the database.

Msg xxx, Level xx, State x, Procedure sp_addextendedproperty, Line xx
Property cannot be added. Property ... already exists for ...

Msg xxx, Level xx, State x, Line x
 Column already has a DEFAULT bound to it.

Msg xxx, Level xx, State x, Line x
 Could not create constraint. See previous errors.

Troubleshoot and Solution

To trouble shoot this error, I decided first to list all constrains in this table, for that I found this helpful post. In my table, all constrains names starts with DF_SANotify so to get all these constrains I executed the following SQL:

SELECT name,OBJECT_NAME(parent_object_id) AS TableName, OBJECT_NAME(object_id) AS ConstraintName
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' and name like 'DF_SANotify_%' order by name

and I got this result:

As you can see .. I found that the constrains are really exist because the table SANotify I am trying to create is already exist but renamed to XXSANotify.

So I deleted the XX table, ran the SQL script and it is executed successful. Instead of deleting the XXSANotify I could just rename it back to SANotify.

Advertisements