tags:

views:

770

answers:

3

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

+1  A: 

You can find the name of the constraint out by sp_help [table name] and then drop it by name.

Or you can probably do this via Management studio.

Tetraneutron
+4  A: 

Or you can find it using sys.check_constraints catalog view.

Alex_L
+4  A: 

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.

adrianbanks