Removing constraints programatically in SQL Server

12Apr 2013 by Ruben Canton

Default Constraint

declare @key nvarchar(200);
    select @key = c.name from sys.all_columns a 
        inner join sys.tables b on  a.object_id = b.object_id
        inner join sys.default_constraints c on a.default_object_id = c.object_id
    where b.name='tablename' and a.name = 'columnname'
exec('alter table tablename drop constraint ' + @key)
 
// in case you wanted to remove the column
alter table tablename drop column columnname

Foreign Key Constraint

SELECT  KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU 
WHERE KCU.TABLE_NAME = 'Message' AND KCU.COLUMN_NAME = 'MaintenanceID'