Drop default constraint on a column in TSQL

I have a table with a column like this that is currently live:

name NVARCHAR(128) NOT NULL DEFAULT ''

I am altering the column like this to make it nullable:

ALTER TABLE  mytable ALTER COLUMN name NVARCHAR(128) NULL

However, the default constraint, named 'DF__mytable__datab__7DE4B36' in one instance of the table, still remains. I know this could have been avoided if the original author named the constraint. I have several of instances of these tables but I don't want to manually delete every constraint in every table I have. What is the easiest and most elegant way of dropping this default constraint on a column in Sql Server that I can uniformily apply to every instance of this table?

EDIT

This is the script that I ended up using:

DECLARE @table_id AS INT
DECLARE @name_column_id AS INT
DECLARE @sql nvarchar(255) 

-- Find table id
SET @table_id = OBJECT_ID('mytable')

-- Find name column id
SELECT @name_column_id = column_id
FROM sys.columns
WHERE object_id = @table_id
AND name = 'name'

-- Remove default constraint from name column
SELECT @sql = 'ALTER TABLE mytable DROP CONSTRAINT ' + D.name
FROM sys.default_constraints AS D
WHERE D.parent_object_id = @table_id
AND D.parent_column_id = @name_column_id
EXECUTE sp_executesql @sql

Another script that can be used to accomplish this can be found here: https://stackoverflow.com/questions/1430456/how-to-drop-sql-default-constraint-without-knowing-its-name

Thanks!

This is how you would drop the constraint

ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, table_name>
   DROP CONSTRAINT <default_constraint_name, sysname, default_constraint_name>
GO

With a script

-- t-sql scriptlet to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

set @database = 'dotnetnuke'
set @table = 'tabs'

DECLARE @sql nvarchar(255)
WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
BEGIN
    select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    where    constraint_catalog = @database and 
            table_name = @table
    exec    sp_executesql @sql
END

Credits go to Jon Galloway http://weblogs.asp.net/jgalloway/archive/2006/04/12/442616.aspx

I would suggest:

DECLARE	@sqlStatement nvarchar(MAX),
		@tableName nvarchar(50) = 'TripEvent',
		@columnName nvarchar(50) = 'CreatedDate';

SELECT					
    @sqlStatement = 'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + dc.name + ';'
FROM			
    sys.default_constraints AS dc
LEFT JOIN	
    sys.columns AS sc ON (dc.parent_column_id = sc.column_id)
WHERE 
    dc.parent_object_id = OBJECT_ID(@tableName)
	AND type_desc = 'DEFAULT_CONSTRAINT'
	AND	sc.name = @columnName

PRINT'   ['+@tableName+']:'+@@SERVERNAME+'.'+DB_NAME()+'@'+CONVERT(VarChar, GETDATE(), 127)+';  '+@sqlStatement;

IF (LEN(@sqlStatement) > 0)
     EXEC sp_executesql @sqlStatement

I would like to refer a previous question, Because I have faced same problem and solved by this [solution][1]. First of all a constraint is always built with a Hash value in it's name. So problem is this HASH is varies in different Machine or Database. For example DF__Companies__IsGlo__6AB17FE4 here 6AB17FE4 is the hash value(8 bit). So I am referring a single script which will be fruitful to all

DECLARE @Command NVARCHAR(MAX)
	 declare @table_name nvarchar(256)
	 declare @col_name nvarchar(256)
	 set @table_name = N'ProcedureAlerts'
	 set @col_name = N'EmailSent'

	 select @Command ='Alter Table dbo.ProcedureAlerts Drop Constraint [' + ( select 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) + ']'

	--print @Command
	exec sp_executesql @Command

It will drop your default constraint. However if you want to create it again you can simply try this

ALTER TABLE [dbo].[ProcedureAlerts] ADD DEFAULT((0)) FOR [EmailSent]

Finally, just simply run a DROP command to drop the column. [1]: https://stackoverflow.com/questions/1430456/how-to-drop-sql-default-constraint-without-knowing-its-name

I would like to offer this suggestion, as it requires only the table name and column name, and clearly indicates if it succeeded or failed, with error if necessary.

-- Removed unnamed default constraint from column if it exists.
-- If the default constraint doesn't exist, no error is raised

DECLARE @tablename AS varchar(128) = 'optional_schema.your_table'
DECLARE @columnname AS varchar(128) = 'your_column'
DECLARE @sql nvarchar(255) = NULL

select @sql = 'ALTER TABLE ' + t.name + ' DROP CONSTRAINT ' + d.name + ';'
FROM sys.columns AS c
INNER JOIN sys.tables t on c.object_id = t.object_id AND t.object_id = OBJECT_ID(@tablename) 
INNER JOIN sys.default_constraints AS D ON c.default_object_id=d.object_id
WHERE c.default_object_id IS NOT NULL
AND c.name = @columnname

--SELECT isNull(@SQL, 'NOT DEFAULTED') AS [Statement] -- Only if you want to see the statement before it runs
IF (@SQL IS NULL) 
	PRINT ' Table ' + @tablename + ', column ' + @columnname + ' does not have a default constraint.'
ELSE
	BEGIN TRY
		EXECUTE sp_executesql @sql
		PRINT @sql + ' -- Successful'
	END TRY
	BEGIN CATCH
		PRINT @sql + ' -- Unsuccessful, Error: ' + ERROR_MESSAGE()
	END CATCH