sql - Dropping unnamed constraints

Question

I've created some foreign keys without an explicit name.

Then I've found SQL generated crazy names like FK__Machines__IdArt__760D22A7. Guess they will be generated with different names at different servers.

Is there any nice function to drop the unnamed FK constraints passing as arguments the tables and the fields in question?

Answer 1

For dropping an individual unnamed default constrain on a column use the following code:

DECLARE @ConstraintName VARCHAR(256)
SET @ConstraintName = (
     SELECT             obj.name
     FROM               sys.columns col 

     LEFT OUTER JOIN    sys.objects obj 
     ON                 obj.object_id = col.default_object_id 
     AND                obj.type = 'F' 

     WHERE              col.object_id = OBJECT_ID('TableName') 
     AND                obj.name IS NOT NULL
     AND                col.name = 'ColunmName'
)   

IF(@ConstraintName IS NOT NULL)
BEGIN
    EXEC ('ALTER TABLE [TableName] DROP CONSTRAINT ['+@ConstraintName+']')
END

If you want to do this for a default column, which is probably more common than the original question and I'm sure a lot of people will land on this from a Google search, then just change the line:

obj.type = 'F'

to

obj.type = 'D'