Cacher is the code snippet organizer for pro developers

PRINT N'Dropping unnamed constraints on [dbo].[Animals]...';

DECLARE @TableName AS NVARCHAR(255)
DECLARE @ColumnName AS NVARCHAR(255)
DECLARE @ConstraintName AS NVARCHAR(255)
DECLARE @DropConstraintSQL AS NVARCHAR(255)

SET @TableName = 'Animals'
SET @ColumnName = 'NumberOfLegs'

--Get the name of the constraint that will be dropped
SET @ConstraintName = 
     (SELECT TOP 1 o.name FROM sysobjects o 
     JOIN syscolumns c 
     ON o.id = c.cdefault 
     JOIN sysobjects t 
     ON c.id = t.id 
     WHERE o.xtype = 'd' 
     AND c.name = @ColumnName 
     AND t.name = @TableName)

SELECT @ConstraintName

--Build a query string that will drop the constraint
SET @DropConstraintSQL = 'ALTER TABLE dbo.' + @TableName + ' DROP ' + @ConstraintName

--Execute the sql to drop the constraint
EXEC (@DropConstraintSQL)