Deleting a Column's Default Constraints

At work I recently had the problem where I needed to drop some columns that had default constraints which had generated names. Here is the set up:

CREATE TABLE ExampleTable ( 
    ExampleColumn int NOT NULL DEFAULT 0,
    OtherColumn int NULL)
GO

ALTER TABLE ExampleTable
    DROP COLUMN ExampleColumn
GO

Msg 5074, Level 16, State 1, Server HENRI-XP\SQLEXPRESS, Line 1
The object 'DF__ExampleTa__Examp__7E6CC920' is dependent on column 'ExampleColumn'.
Msg 4922, Level 16, State 9, Server HENRI-XP\SQLEXPRESS, Line 1
ALTER TABLE DROP COLUMN ExampleColumn failed because one or more objects access this column.

At this point I could have just dropped the constraint using the name in the error message, however I had to apply the same change to multiple databases with the same schema. Of course, the generated name was different for each database.

I ended up writing a procedure that would look up the default constraint given a table name and column name in the sys views and drop it. It's pretty simple here is the code:

CREATE PROC dbo.DropDefaultConstraint
    @TableName sysname,
    @ColumnName sysname
AS
    DECLARE @defaultConstraintName sysname;

    SELECT @defaultConstraintName = OBJECT_NAME(default_object_id)
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@TableName) and name = @ColumnName

    IF(@defaultConstraintName IS NOT NULL)
    BEGIN
        EXEC ('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @defaultConstraintName);
    END;
GO

Of course, this isn't the kind of procedure you'd actually deploy to a production server, but it's handy anyway.