How can I drop all the default constraints constraints on a table

Question

Hi All, How can I drop all the default constraints belonging to a particular table in SQL 2005?

Answer 1

One solution from a search: (Edited for Default constraints)

SET NOCOUNT ON

DECLARE  @constraintname SYSNAME, @objectid int,
           @sqlcmd         VARCHAR(1024)

DECLARE CONSTRAINTSCURSOR CURSOR  FOR
SELECT NAME, object_id
FROM   SYS.OBJECTS
WHERE  TYPE = 'D' AND @objectid = OBJECT_ID('Mytable')

OPEN CONSTRAINTSCURSOR

FETCH NEXT FROM CONSTRAINTSCURSOR
INTO @constraintname, @objectid

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @sqlcmd = 'ALTER TABLE ' + OBJECT_NAME(@objectid) + ' DROP CONSTRAINT ' + @constraintname
    EXEC( @sqlcmd)
    FETCH NEXT FROM CONSTRAINTSCURSOR
    INTO @constraintname, @objectid
END

CLOSE CONSTRAINTSCURSOR
DEALLOCATE CONSTRAINTSCURSOR

Answer 2

Just why do you want to do this? Dropping constraints is a pretty drastic action and affects all users not just your process. Maybe your problem can be solved some other way. If you aren't the dba of the system, you should think very hard about whether you should do this. (Of course in most systems, a dba wouldn't allow anyone else the permissions to do such a thing.)