Orphan constraints in SQL Server database

Question

Running the following query results in all constraints in our client's database. However, several rows in the result set don't seem to have a parent, i.e. parent_object_id = 0 and OBJECT_NAME(parent_object_id) returns NULL.

SELECT name, type_desc, OBJECT_NAME(parent_object_id), parent_object_id
FROM sys.objects
WHERE is_ms_shipped = 0
AND type_desc LIKE '%_CONSTRAINT'

Does this mean there are orphan constraints in the database? If so, how do I remove these?

From their names, I can see they are leftovers from before a large amount changes were made to the structure.

Answer 1

Using sp_helptext I can see they were created using a CREATE DEFAULT statement, e.g.:

CREATE DEFAULT dbo.MyDefault AS 2

This means they are simply unbound defaults, which can be bound using the sp_binddefault according to MSDN, and removed with a simple DROP DEFAULT statement:

DROP DEFAULT dbo.MyDefault

I wasn't aware of this syntax, which apparently will be removed in a future version of SQL Server, according to the aforementioned MSDN article.