How to drop SQL default constraint without knowing its name?

In Microsoft SQL Server, I know the query to check if a default constraint exists for a column and drop a default constraint is:

IF EXISTS(SELECT * FROM sysconstraints
  WHERE id=OBJECT_ID('SomeTable')
  AND COL_NAME(id,colid)='ColName'
  AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1)    
ALTER TABLE SomeTable DROP CONSTRAINT DF_SomeTable_ColName

But due to typo in previous versions of the database, the name of the constraint could be DF_SomeTable_ColName or DF_SmoeTable_ColName.

How can I delete the default constraint without any SQL errors? Default constraint names don't show up in INFORMATION_SCHEMA table, which makes things a bit trickier.

So, something like 'delete the default constraint in this table/column', or 'delete DF_SmoeTable_ColName', but don't give any errors if it can't find it.

Expanding on Mitch Wheat's code, the following script will generate the command to drop the constraint and dynamically execute it.

declare @schema_name nvarchar(256)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @schema_name = N'MySchema'
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select @Command = 'ALTER TABLE ' + @schema_name + '.[' + @table_name + '] DROP CONSTRAINT ' + d.name
 from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id
 where t.name = @table_name
  and t.schema_id = schema_id(@schema_name)
  and c.name = @col_name

--print @Command

execute (@Command)

Rob Farley's blog post might be of help:

• Two ways to find / drop a default constraint without knowing its name

Something like:

 declare @table_name nvarchar(256)
 declare @col_name nvarchar(256)
 set @table_name = N'Department'
 set @col_name = N'ModifiedDate'
 
 select t.name, c.name, d.name, d.definition
 from 
     sys.tables t
     join sys.default_constraints d on d.parent_object_id = t.object_id
     join sys.columns c on c.object_id = t.object_id
                           and c.column_id = d.parent_column_id
 where 
     t.name = @table_name
     and c.name = @col_name

I found that this works and uses no joins:

DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID('[tableSchema].[tableName]') AND [name] = 'columnName';
EXEC('ALTER TABLE [tableSchema].[tableName] DROP CONSTRAINT ' + @ObjectName)

Just make sure that columnName does not have brackets around it because the query is looking for an exact match and will return nothing if it is [columnName].

To drop constraint for multiple columns:

declare @table_name nvarchar(256)

declare @Command nvarchar(max) = ''

set @table_name = N'ATableName'

select @Command = @Command + 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name + CHAR(10)+ CHAR(13)
from sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
     and c.column_id = d.parent_column_id
where t.name = @table_name and c.name in ('column1','column2','column3')

--print @Command

execute (@Command)

Expanded solution (takes table schema into account):

-- Drop default contstraint for SchemaName.TableName.ColumnName
DECLARE @schema_name NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)
DECLARE @col_name NVARCHAR(256)
DECLARE @Command  NVARCHAR(1000)

set @schema_name = N'SchemaName'
set @table_name = N'TableName'
set @col_name = N'ColumnName'

SELECT @Command = 'ALTER TABLE [' + @schema_name + '].[' + @table_name + '] DROP CONSTRAINT ' + d.name
 FROM sys.tables t   
  JOIN sys.default_constraints d       
   ON d.parent_object_id = t.object_id  
  JOIN sys.schemas s
		ON s.schema_id = t.schema_id
  JOIN    sys.columns c      
   ON c.object_id = t.object_id      
    AND c.column_id = d.parent_column_id
 WHERE t.name = @table_name
	AND s.name = @schema_name 
  AND c.name = @col_name

EXECUTE (@Command)

Run this command to browse all constraints:

exec sp_helpconstraint 'mytable' --and look under constraint_name. 

It will look something like this: DF__Mytable__Column__[ABC123]. Then you can just drop the constraint.

Drop all default contstraints in a database - safe for nvarchar(max) threshold.

/* WARNING: THE SAMPLE BELOW; DROPS ALL THE DEFAULT CONSTRAINTS IN A DATABASE */ 
/* MAY 03, 2013 - BY WISEROOT  */
declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @df_name nvarchar(128)
declare @cmd nvarchar(128) 

declare table_names cursor for 
 SELECT t.name TableName, c.name ColumnName
 FROM sys.columns c INNER JOIN
	 sys.tables t ON c.object_id = t.object_id INNER JOIN
	 sys.schemas s ON t.schema_id = s.schema_id
	 ORDER BY T.name, c.name

	 open table_names
fetch next from table_names into @table_name , @column_name
while @@fetch_status = 0
BEGIN

if exists (SELECT top(1) d.name from sys.tables t join sys.default_constraints d on d.parent_object_id = t.object_id join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id where t.name = @table_name and c.name = @column_name)
BEGIN
	SET @df_name = (SELECT top(1) d.name from sys.tables t join sys.default_constraints d on d.parent_object_id = t.object_id join sys.columns c on c.object_id = t.object_id and c.column_id = d.parent_column_id where t.name = @table_name and c.name = @column_name)
	select @cmd = 'ALTER TABLE [' + @table_name +  '] DROP CONSTRAINT [' +  @df_name + ']'
	print @cmd
	EXEC sp_executeSQL @cmd;
END

  fetch next from table_names into @table_name , @column_name
END

close table_names 
deallocate table_names

I hope this could be helpful for whom has similar problem . In ObjectExplorer window, select your database=> Tables,=> your table=> Constraints. If the customer is defined on create column time, you can see the default name of constraint including the column name. then use:

ALTER TABLE  yourTableName DROP CONSTRAINT DF__YourTa__NewCo__47127295;

(the constraint name is just an example)

Following solution will drop specific default constraint of a column from the table

Declare @Const NVARCHAR(256)

SET @Const = (
              SELECT TOP 1 'ALTER TABLE' + YOUR TABLE NAME +' DROP CONSTRAINT '+name
              FROM Sys.default_constraints A
              JOIN sysconstraints B on A.parent_object_id = B.id
              WHERE id = OBJECT_ID('YOUR TABLE NAME')
              AND COL_NAME(id, colid)='COLUMN NAME'
              AND OBJECTPROPERTY(constid,'IsDefaultCnst')=1
            )
 EXEC (@Const)

I had some columns that had multiple default constraints created, so I create the following stored procedure:

CREATE PROCEDURE [dbo].[RemoveDefaultConstraints] @table_name nvarchar(256), @column_name nvarchar(256)
AS
BEGIN

	DECLARE @ObjectName NVARCHAR(100)

	START: --Start of loop
	SELECT 
		@ObjectName = OBJECT_NAME([default_object_id]) 
	FROM 
		SYS.COLUMNS
	WHERE 
		[object_id] = OBJECT_ID(@table_name) 
		AND [name] = @column_name;

	-- Don't drop the constraint unless it exists
	IF @ObjectName IS NOT NULL
	BEGIN
		EXEC ('ALTER TABLE '+@table_name+' DROP CONSTRAINT ' + @ObjectName)
		GOTO START; --Used to loop in case of multiple default constraints
	END
END
GO

-- How to run the stored proc.  This removes the default constraint(s) for the enabled column on the User table.
EXEC [dbo].[RemoveDefaultConstraints] N'[dbo].[User]', N'enabled'
GO

-- If you hate the proc, just get rid of it
DROP PROCEDURE [dbo].[RemoveDefaultConstraints]
GO

Useful for some columns that had multiple default constraints or check constraints created:

Modified https://stackoverflow.com/a/16359095/206730 script

> Note: this script is for sys.check_constraints

declare @table_name nvarchar(128)
declare @column_name nvarchar(128)
declare @constraint_name nvarchar(128)
declare @constraint_definition nvarchar(512)

declare @df_name nvarchar(128)
declare @cmd nvarchar(128) 

PRINT 'DROP CONSTRAINT [Roles2016.UsersCRM].Estado'

declare constraints cursor for 
 select t.name TableName, c.name ColumnName, d.name ConstraintName, d.definition ConstraintDefinition
 from sys.tables t   
 join sys.check_constraints d  on d.parent_object_id = t.object_id  
 join sys.columns c  on c.object_id = t.object_id      
 and c.column_id = d.parent_column_id
 where t.name = N'Roles2016.UsersCRM' and c.name = N'Estado'

open constraints
fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
while @@fetch_status = 0
BEGIN
    print 'CONSTRAINT: ' + @constraint_name
    select @cmd = 'ALTER TABLE [' + @table_name +  '] DROP CONSTRAINT [' +  @constraint_name + ']'
    print @cmd
    EXEC sp_executeSQL @cmd;

  fetch next from constraints into @table_name , @column_name, @constraint_name, @constraint_definition
END

close constraints 
deallocate constraints

Always generate script and review before you run. Below the script

  select 'Alter table dbo.' + t.name + ' drop constraint '+ d.name  
  from sys.tables t
  join sys.default_constraints d on d.parent_object_id = t.object_id
  join sys.columns c on c.object_id = t.object_id
       and c.column_id = d.parent_column_id
  where c.name in ('VersionEffectiveDate','VersionEndDate','VersionReasonDesc')
  order by t.name

declare @ery nvarchar(max)
declare @tab nvarchar(max) = 'myTable'
declare @qu nvarchar(max) = 'alter table '+@tab+' drop constraint '

select @ery = (select bj.name from sys.tables as tb 
inner join sys.objects as bj 
on tb.object_id = bj.parent_object_id
where tb.name = @tab and bj.type = 'PK')

exec(@qu+@ery)

Take a look.

declare @table_name nvarchar(100)
declare @col_name nvarchar(100)
declare @constraint nvarchar(100)
set @table_name = N'TableName'
set @col_name = N'ColumnName'

IF EXISTS (select		c.*
	from		sys.columns c 
	inner join	sys.tables t on t.object_id = c.object_id
	where		t.name = @table_name
	and			c.name = @col_name) 
BEGIN

select @constraint=d.name
from 
sys.tables t
join sys.default_constraints d on d.parent_object_id = t.object_id
join sys.columns c on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where 
t.name = @table_name
and c.name = @col_name

	IF LEN(ISNULL(@constraint, '')) <> 0
	BEGIN
		DECLARE @sqlcmd VARCHAR(MAX)
		SET @sqlcmd = 'ALTER TABLE ' + QUOTENAME(@table_name) + ' DROP CONSTRAINT' + 
        QUOTENAME(@constraint);
		EXEC (@sqlcmd);
		
	END

END
GO