SQL Server 2000 drop column with constraints

Question

I have the same problem as described in this question, but there it's SQL Server 2005 and the "accepted" answer doesn't work in SQL Server 2000.

Specifically: I'm trying to run ALTER TABLE foo DROP COLUMN bar, and it's failing because there's a "default constraint." Meaning, I have a default value on that column which SQL Server implements as a separate constraint that I need to delete first.

The problem is no name was given for the default constraint when the column was created, so I have to query the system tables to discover the (auto-generated) name of the constraint.

The answer given in that other question works for me in SQL Server 2005 but not in SQL Server 2000. I need the latter.

[UPDATE] I need a query that can answer the question "What is the name of the default constraint for column bar in table foo." Not a way for a human being to manually find the answer.

Answer 1

sp_help 'tablename'

gives you a bunch of info on the table - including all the constraints and the constraint names

Actually sp_help calls this:

sp_helpconstraint 'tablename', nomsg

Default constraints will say "DEFAULT on column xxx"

Answer 2

To get a list of the default constraints for a table, try this

select * from sysobjects [constraint] 
 join sysobjects [table] on [constraint].parent_obj = [table].id 
where [constraint].type = 'D'
 and [table].name = 'table_name'
--another option: and [table].id = OBJECT_ID(N'dbo.table_name')

Answer 3

Just figured out what the referenced SQL 2005 query was actually doing. Here's a replication of that query that works in SQL 2000

select 
    col.name, 
    col.colorder, 
    col.cdefault, 
    OBJECTPROPERTY(col.cdefault, N'IsDefaultCnst') as is_defcnst, 
    dobj.name as def_name
from syscolumns col 
    left outer join sysobjects dobj 
        on dobj.id = col.cdefault and dobj.type = 'D' 
where col.id = object_id(N'dbo.table_name') 
and dobj.name is not null

Answer 4

This one is constructed over the first solution provided by bdukes, mixed with information_schema.columns, it seems that info contains the ordinal position of the column the default belongs

SELECT name 
FROM sysobjects [constraint] 
 JOIN sysobjects [table] on [constraint].parent_obj = [table].id
 JOIN information_schema.columns col ON [table].name = col.table_name AND [constraint].info = col.ordinal_position
WHERE [constraint].type = 'D'
 AND [table].name = 'table_name'
 AND col.column_name = 'column_name'