Small Things Learned Today

Drop a Default Constraint on a Table Before Dropping the Table.

Trying to drop the colum from a database via a simple script

ALTER TABLE [dbo].MYTABLENAME DROP COLUMN MYCOLUMNNAME

I came across the error I did not quite expect.

Msg 5074, Level 16, State 1, Line 1
The object 'DF__MYTABLENAME__MYCOL__42ACE4D4' is dependent on column 'MYCOLUMNNAME'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN MYCOLUMNNAME failed because one or more objects access this column.

Turns out it is not possible to drop the column this way because there is a default constraint set on it.
However, the constraint name is 'DF__MYTABLENAME__MYCOL__42ACE4D4', which is fine if I'm doing it in the database I have full access to. I could even use the visual tool like Management Studio to just right-click and drop.

That's not possible, however, in my case, because the database is in the remote location and I need to write a script that I can send over and ask the person in charge to run it. I don't want to ask him 'hey, could you please run sp_help on the table MYTABLENAME and let me know what it tells you'.
That's where this little script comes handy:

declare @default sysname, @sql nvarchar(max)

select @default = name 
from sys.default_constraints 
where parent_object_id = object_id('MYTABLENAME')
AND type = 'D'
AND parent_column_id = (
 select column_id 
 from sys.columns 
 where object_id = object_id('MYTABLENAME')
 and name = 'MYCOLUMNNAME'
 )

set @sql = N'alter table MYTABLENAME drop constraint ' + @default
exec sp_executesql @sql

alter table MYTABLENAME drop column MYCOLUMNNAME

go

by Evgeny. Also posted on my website