Oct 30

Drop All SQL Server Default Constraints on a Table

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),
        @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