Drop constraint for Default Value

Assign a default value to a column via  T-SQL scripts is really simple. Its deletion, instead, isn't so simple 'cause of the variable constraint name (in a multi-server environment or on different environments it will never have the same name).

This script retrieve the constraint name and drop it.


DECLARE @name nvarchar(100)

SELECT @name =  name FROM sys.objects WHERE type = 'D' AND object_id IN
(
SELECT default_object_id
FROM [sys].[all_columns]
WHERE name = 'column_name'
AND object_id =
(
SELECT  object_id FROM sys.objects WHERE type = 'U' AND name = 'table_name'
)
)

EXEC ('ALTER TABLE table_name DROP CONSTRAINT ' + @name)