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'

Answer 2

There is not a built in procedure to accomplish this, but you can build your own using the information in the information_schema views.

Table based example

Create Proc dropFK(@TableName sysname)
as
Begin

Declare @FK sysname
Declare @SQL nvarchar(4000)
Declare crsFK cursor for

select tu.Constraint_Name from 
information_schema.constraint_table_usage TU
LEFT JOIN SYSOBJECTS SO 
ON TU.Constraint_NAME = SO.NAME
where xtype = 'F'
and Table_Name = @TableName
open crsFK
fetch next from crsFK into @FK
While (@@Fetch_Status = 0)
Begin
    Set @SQL = 'Alter table ' + @TableName + ' Drop Constraint ' + @FK
    Print 'Dropping ' + @FK
    exec sp_executesql  @SQL
    fetch next from crsFK into @FK
End
Close crsFK
Deallocate crsFK
End