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

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.

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.

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.

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

For a single table and column in a single line use the following

declare @sql nvarchar(max); set @sql = ''; SELECT @sql+='ALTER TABLE [dbo].[YOURTABLENAME] DROP CONSTRAINT ' + ((SELECT OBJECT_NAME(constid) FROM sysconstraints WHERE OBJECT_NAME(id) = 'YOURTABLENAME'AND colid IN (SELECT ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name = 'YOURTABLENAME' and COLUMN_NAME = 'YOURCOLUMNNAM'))) + ';'; EXEC sp_executesql @sql;

If you have multiple constraints on the column you will need to discriminate on the constraint you are after, but if you just have a default constraint this will do the trick.

Check out the other columns available in the information_schema to allow you to discriminate further.

Here goes my own version that drops all dependent constraints -- default constraint (if exists) and all affected check constraints (as SQL standard seems to suggest and as some other databases seem to so)

declare @constraints varchar(4000);
declare @sql varchar(4000);
with table_id_column_position as (
   select object_id table_id, column_id column_position
      from sys.columns where object_id is not null and object_id = object_id('TableName') and name = 'ColumnToBeDropped'
)
select @constraints = coalesce(@constraints, 'constraint ') + '[' + name + '], '
from sysobjects 
where (
  -- is CHECK constraint
  type = 'C' 
  -- dependeds on the column
  and id is not null
  and id in (
      select object_id --, object_name(object_id)
      from sys.sql_dependencies, table_id_column_position 
      where object_id is not null
      and referenced_major_id = table_id_column_position.table_id
      and referenced_minor_id = table_id_column_position.column_position
    )
) OR (
  -- is DEFAULT constraint
  type = 'D'
  and id is not null
  and id in (
    select object_id
    from sys.default_constraints, table_id_column_position
     where object_id is not null
     and parent_object_id = table_id_column_position.table_id
     and parent_column_id = table_id_column_position.column_position
  )
);
set @sql = 'alter table TableName drop ' + coalesce(@constraints, '') + ' column ColumnToBeDropped';
exec @sql

(Beware: both TableName and ColumnToBeDropped appear twice in the code above)

This works by constructing single ALTER TABLE TableName DROP CONSTRAINT c1, ..., COLUMN ColumnToBeDropped and executing it.