is there a "alter table xxxx drop default yyy" ?

Hi:
I need to change a column's datatype from tinyint to int as follows:
alter table tableName
alter column column1 int

but with error <<'DF__LandMarks__color__6A50C1DA' is depending on it.>>

However, this old default is not part of the constraint. Thus, the only way is to delete it from sysobjects.

unfortunately, the following code I have to commented since they could only be executed line by line, not within a begin...end block.
--exec master.dbo.sp_configure 'allow updates', 1
--reconfigure with override
--delete from sysobjects
-- where name = 'DF__LandMarks__color__6A50C1DA'
-- and type = 'D'
--exec master.dbo.sp_configure 'allow updates', 0
--reconfigure with override

I need to update 10 server around 2500 databases with this change.
I have looked INFORMATION_SCHEMA related views, but not found default related, maybe I missed something.

thanks
David

I strongly recommend that you do not use the code in your post (unless you want to corrupt your databases of course).

Defaults are actually constraints, so what you are really after is ALTER TABLE dbo.YourTable
DROP CONSTRAINT DF__LandMarks__color__6A50C1DA

Stephen:

It works. The thing puzzles me is why the INFORMATION_SCHEMA.TABLE_CONSTRAINTS did not show this default, which leaded me think "it is not part of the constraint" and went to the sysobjects wrong direction.

Does it mean the INFORMATION_SCHEMA.TABLE_CONSTRAINTS does not include the default as constraints, but the alter table drop constraint do reconganize the default?
Not quite consistent....

thanks for the tip.
-D

I agree. It's not consistent. A default is a constraint though. You can get it by querying the sysconstraints table. Or you could write your own version of the Table_Constraints view that includes xtype = 'D'

Hi Rawhide and all

thanks for the clarification.

Now, I am using the sysobjects and syscolumns to retrieve the default name, since each database comes out with different default name for this table and the coulmn.

p.s. I noticed from Enterprise Manager, there are some database at the table design window, the datatype of the field are still tinyint after script alter column changes. While at isql/w with sp_help tableName, the field IS changed as int. Even after 'refresh' on both database and table selection.

-D