How do you drop a default value or similar constraint in T-SQL?

Question

I know the syntax:

ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint] 

but how to I drop the default constraint when I don't know its name? (That is, it was autogenerated at CREATE TABLE time.)

Answer 1

You can use this code to do it automatically:

DECLARE @tableName VARCHAR(MAX) = '<MYTABLENAME>' DECLARE @columnName VARCHAR(MAX) = '<MYCOLUMNAME>' DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = Name  FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName)  AND PARENT_COLUMN_ID = (     SELECT column_id FROM sys.columns     WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName)) IF @ConstraintName IS NOT NULL     EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName) 

Just replace <MYTABLENAME> and <MYCOLUMNNAME> as appropriate.

Answer 2

If you want to do this manually, you can use Management Studio to find it (under the Constraints node inside the table).

To do it using SQL:

• If the constraints are default constraints, you can use sys.default_constraints to find it:

  SELECT OBJECT_NAME(parent_object_id) AS TableName, name AS ConstraintName FROM sys.default_constraints ORDER BY TableName, ConstraintName 

• If you are looking for other constraints as well (check, unique, foreign key, default, primary key), you can use sysconstraints:

  SELECT OBJECT_NAME(id) AS TableName, OBJECT_NAME(constid) AS ConstraintName FROM sysconstraints ORDER BY TableName, ConstraintName 

You do not say which version of SQL Server you are using. The above work on both SQL 2005 and SQL 2008.