drop column fails

Hi all,
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>