Friday, 7 May 2010

Drop Default Constraint

The basic syntax for dropping a constraint is quite simple:
ALTER TABLE {{MYTABLENAME}} DROP CONSTRAINT {{CONSTRAINTNAME}}
The problem comes if you have created unnamed constraints as SQL will have assigned a random name. You need to discover the constraint name before you can drop it.
To discover the constraint name:
SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = '{{MYTABLENAME}}' AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id('dbo.{{MYTABLENAME}}') AND name = '{{COLUMNNAME}}')
To discover and drop the constraint:
DECLARE @defname VARCHAR(100), @cmd VARCHAR(1000);
SET @defname = (SELECT name FROM sysobjects so JOIN sysconstraints sc ON so.id = sc.constid WHERE object_name(so.parent_obj) = '{{MYTABLENAME}}' AND so.xtype = 'D' AND sc.colid = (SELECT colid FROM syscolumns WHERE id = object_id('dbo.{{MYTABLENAME}}') AND name = '{{COLUMNNAME}}'));
SET @cmd = 'ALTER TABLE {{MYTABLENAME}} DROP CONSTRAINT ' + @defname;
EXEC(@cmd);
GO
alter table {{MYTABLENAME}} drop column {{COLUMNNAME}}
GO

No comments: