How to drop foreign keys of a particular column

Question

I have created a foreign key without specifying the name so sql server has created it with auto generated name. Now I want to drop the column which has this foreign key. The problem is that i don't know the name of this foreign key. Is there any way to drop all the foreign keys for particular column in particular table?

So far I've found this script which drops all default constraints for column

DECLARE @tableName VARCHAR(MAX)
DECLARE @ConstraintName nvarchar(200)
DECLARE @columnName VARCHAR(MAX)

SET @tableName = 'tablename'
SET @columnName = 'columnname'
SELECT @ConstraintName = Name
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    BEGIN
 EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)
 END

ALTER TABLE [tablename] DROP COLUMN columnname
GO

But it doesn't help with foreign key constraints.

Answer 1

If you want to get more information about FK and specifically about a particular scheme and table than you can use.

 SELECT 
    t.Name as TableName,
    c.name as ColumnName, 
    fk.name as FK_NAME 

 FROM sys.foreign_keys as fk
 inner join sys.tables as t on fk.parent_object_id = t.object_id
 inner join sys.columns as c on c.object_id = t.object_id
 inner join sys.schemas as sc on t.schema_id = sc.schema_id
 WHERE sc.name = 'Schema' and t.name = 'Table' and c.name = 'Column'

If you are interested only about certain column then u can use Ross Presser answer.

Also if you want to drop all fk constraint you can execute this:

  Declare @sql  nvarchar(4000)
  SET @sql = N'';

  SELECT @sql = @sql + '
  ALTER TABLE [' + sc.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP  CONSTRAINT ' + '[' + fk.NAME + ']
   '
  FROM sys.foreign_keys as fk
  inner join sys.tables as t on fk.parent_object_id = t.object_id
  inner join sys.columns as c on c.object_id = t.object_id
  inner join sys.schemas as sc on t.schema_id = sc.schema_id
  WHERE sc.name = 'schemaName'  and c.name = 'columnName' -- you can include and fk name
  ORDER BY fk.NAME
  PRINT @sql;
--EXEC sys.sp_executesql @sql; 

Answer 2

You can get all the constraint list along with the names assigned to particular tables with the help of the below query:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='YourTableName';

Once you find the constraint you want to delete you can do it with the help of below query:

ALTER TABLE Orders
DROP CONSTRAINT constraint_name;

Answer 3

You can find all constraints throughout the database that use the column:

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
WHERE CCU.COLUMN_NAME='someColumn'

The output gives the constraint name and the table it is attached to.

Answer 4

Here's an example to find foreign keys using the system views (which aren't amazingly well documented):

select  fk.name as ForeignKeyName
    ,   t_parent.name as ParentTableName
    ,   c_parent.name as ParentColumnName
    ,   t_child.name as ReferencedTableName
    ,   c_child.name as ReferencedColumnName
from sys.foreign_keys fk 
inner join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id
inner join sys.tables t_parent on t_parent.object_id = fk.parent_object_id
inner join sys.columns c_parent on fkc.parent_column_id = c_parent.column_id  
                        and c_parent.object_id = t_parent.object_id 
inner join sys.tables t_child on t_child.object_id = fk.referenced_object_id
inner join sys.columns c_child on c_child.object_id = t_child.object_id
                            and fkc.referenced_column_id = c_child.column_id
order by t_parent.name, c_parent.name