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;