Drop Column and Its Default Constraint

Dropping a column that has a default constraint fails unless you drop the constraint first - and the constraint's name is something you rarely know. This little stored procedure looks up the default constraint on the given table and column, drops it if one exists, and then drops the column itself.

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

-- =============================================
-- Author:		Josh Patterson
-- Create date: 7/20/2006
-- Description:	This SP will drop the specified column and it's default value constraint (if it exists).
-- =============================================
CREATE PROCEDURE DropColumn
(
	@Table	NVARCHAR(255),
	@Column	NVARCHAR(255)
)
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

	SET @SQL = 'ALTER TABLE [' + @Table + '] DROP COLUMN [' + @Column + ']'
	EXEC(@SQL)
END
GO