SQL Server 2000 drop column with constraints

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.

From stackoverflow

• 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"

  Jason Cohen : I run that and get a pile of ResultSet objects. How do I determine which constraint is for default values? I can see as a human, but what's the right *query* I can do programmatically?

  Jason Cohen : OK... is it possible to reduce this to a single query that will return one row with the answer (if there is a default constrain), or no rows if there's no answer? Or at least a single result-set where it's easy to find the answer given a column name?

• 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')
  

  Jason Cohen : That looks close (+1), but I don't see the column name in those rows. How do I figure out which row goes with which column?

• 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
  

  Jason Cohen : Looks good. That appears to also work in SQL Server 2005 -- should it? You're using col.cdefault instead of col.default_object_id -- are those BOTH in 2005?

  bdukes : SQL Server 2005 has compatibility views that mimic the system tables in SQL Server 2000. So, while the 2005 script uses sys.objects, SQL 2000 uses sysobjects (without the dot), which is also available as a compatibility view in SQL Server 2005. Does that make sense?

  Jason Cohen : Makes sense, thanks. Subtle!

• 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'