Drop default constraint which has an illegal name?

Question

I have several default constraints which now have illegal names due to a name-normalization script error (apparently sp_rename does a poor job of validating the newname parameter).

Some columns have actual default names in the format

[[DF_SomeTable_SomeColumn]] 

or

[dbo.[DF_SomeOtherDefault]]

Neither sp_rename nor Alter Table Drop Constraint can remove them as they are reported as syntax errors.

How can I get rid of them, or rename them?

Answer 1

The easiest way is probably to have SQL give you the correctly escaped constraint names using the quotename function. You can query the catalog views to generate the drop statements for you based on the table names. As an example:

-- this weirdness will create a default constraint called [[my weird default]]
create table t(i int constraint [[[my weird default]]]]] default (1))

-- find all the constraints with square bracket literals on the tables I care about
select  concat('alter table [', t.name, '] drop constraint ', quotename(c.name))
from    sys.tables                  t
join    sys.default_constraints c   on  c.parent_object_id = t.object_id
where   t.name = 't'
and     c.name like '%[[]%'


-- returns: alter table [t] drop constraint [[[my weird default]]]]]

Obviously, add the appropriate filtering to your query.