A few years ago, I wrote a blog post about scripting the drop of unknown default constraints in SQL server. Today, I came across a similar problem but with unnamed foreign key constraint. Below is the SQL command needed for find the name of a foreign key constraint given the table and column names.
declare @fc_name varchar(max)
select @fc_name = o.name from sys.tables t
join sys.foreign_key_columns fk
on fk.parent_object_id = t.object_id
join sys.columns c
on c.object_id = t.object_id
and c.column_id = fk.parent_column_id
join sys.objects o
on o.object_id = fk.constraint_object_id
where t.name = 'some_db_table'
and c.name = 'some_column_in_table'
if @fc_name is not null
begin
declare @sql varchar(max) = 'alter table some_db_table ' + ' drop constraint ' + @fc_name
exec (@sql)
end