My Technical Notes

Wednesday, 25 February 2015

SQL Server: dropping a Default Constraint

The following script, with two parameters `table` and `column`, will delete the default constraint on that table:


DECLARE @table NVARCHAR(MAX) = 'dbo.Project';              -- change value
DECLARE @column NVARCHAR(MAX) = 'RiskAssessmentCompleted'; -- change value

DECLARE @constraintName NVARCHAR(MAX) =
    (
        SELECT OBJECT_NAME([default_object_id])
        FROM sys.columns
        WHERE name = @column AND [object_id] = OBJECT_ID(@table)
    )
    
EXEC('ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName)

If you would like to specify the constraint name when adding a column, you can do:


ALTER TABLE dbo.MyTable 
ADD MyTestColumn BIT NOT NULL 
CONSTRAINT MyColConstraintName DEFAULT(0)
Thereafter, deleting the constraint with:

ALTER TABLE dbo.MyTable
DROP CONSTRAINT MyColConstraintName

No comments: