Menu
How can I drop all the default constraints belonging to a particular table in SQL 2005?
608
To drop (table) check constraints, use the DROP CHECK clause of the ALTER TABLE statement. When you drop a check constraint, all packages and cached dynamic statements with INSERT or UPDATE dependencies on the table are invalidated. The name of all check constraints on a table can be found in the SYSCAT.
Easy way is use sp_help tablename. All constraints will be listed. Then use ALTER TABLE DROP CONSTRAINT to drop them.
The DROP DEFAULT command is used to delete a DEFAULT constraint.
To add a DEFAULT constraint to an existing column, use the ALTER TABLE statement and specify the column and the specific constraint that you want to apply.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
