Search code examples
sqlt-sqlconstraintsdefault

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

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.)

Solution

  • 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.