Drop a Column's Default Constraint

The companion to the DropColumn procedure: this one only drops the default constraint on a given column, leaving the column itself in place. Useful when you want to change or remove a default without touching the data.

Example: EXEC DropColumnDefault @Table = 'Customers', @Column = 'LegacyFlag'

-- =============================================
-- Author:		Josh Patterson
-- Create date: 7/20/2006
-- Description:	This SP will drop the specified column's default value constraint (if it exists).
-- =============================================
CREATE PROCEDURE DropColumnDefault
(
	@Table	VarChar(MAX),
	@Column	VarChar(MAX)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @SQL VarChar(MAX)

	SELECT
		@SQL = 'ALTER TABLE [' + @Table + '] DROP CONSTRAINT [' + DFValues.Name + ']'
	FROM
		Sys.Objects AS Tables
	INNER JOIN Sys.Columns AS Columns
		ON Columns.Object_ID = Tables.Object_ID
	INNER JOIN Sys.Objects AS DFValues
		ON DFValues.Object_ID = Columns.Default_Object_ID
	WHERE
		Tables.Name = @Table
		AND Columns.Name = @Column

	IF NOT @SQL IS NULL
	BEGIN
		EXEC(@SQL)
	END
END
GO