Recently I was working on updating some tables, and the SQL that was generated for the update included some code that looked like this:

ALTER TABLE [schema].[table]
    DROP CONSTRAINT [DF__Table__Column__a789cv78s7s]

Well, it dawned on me that the various environments we have *COULD* have different constraint names.

Now, these constraints will be deleted when the table is dropped, so an explicit drop is not necessarily required.

However, if you actually name your default constraints, rather than utilize the shorthand in the table definition, then you may run into an issue of duplicate default constraint names.

This handy script will drop all of the defaults on a particular table.

DECLARE @SchemaName NVARCHAR(256),
        @TableName NVARCHAR(256),
        @SQL NVARCHAR(MAX),
        @NewLine CHAR(1)

SELECT  @SchemaName = N'dbo',
        @TableName = N'TheNameOfTheTable',
        @NewLine = CHAR(10)

SELECT  @SQL = ISNULL(@SQL + @NewLine, '') + 
            'ALTER TABLE [' + S.name + '].[' + T.name + '] ' +
                'DROP CONSTRAINT [' + D.name + ']'
FROM    sys.tables T
    INNER JOIN sys.default_constraints D
        ON D.parent_object_id = T.object_id
    INNER JOIN sys.columns C
        ON C.object_id = T.object_id
            AND C.column_id = D.parent_column_id
    INNER JOIN sys.schemas S
        ON T.schema_id = S.schema_id
WHERE   S.name = @SchemaName
  AND   T.name = @TableName

PRINT @SQL
EXECUTE (@SQL)

RJ Hatch

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.