Dropping unnamed constraints

Question

Being a rookie, I've created some foreign keys without an explicit name.

Then i've founded 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

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